# Excel question - dynamic row reference within a formula
**URL:** https://boards.straightdope.com/t/excel-question-dynamic-row-reference-within-a-formula/623815
**Category:** Factual Questions
**Created:** [June 1, 2012, 9:27pm UTC](https://boards.straightdope.com/t/excel-question-dynamic-row-reference-within-a-formula/623815 "2012-06-01T21:27:10Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@Lance\_Steele](https://boards.straightdope.com/u/Lance_Steele)
#### Post date: [June 1, 2012, 9:27pm UTC](https://boards.straightdope.com/t/excel-question-dynamic-row-reference-within-a-formula/623815/1 "2012-06-01T21:27:10Z")
Is there a way to make the row number that a formula looks at itself the result of a formula? For example, I want to do a countif in a horizontal range of cells. I want the row number of that range to be the result of a vlookup. My coworker and I have been researching this all morning to no avail.
---
### Author:  [@am77494](https://boards.straightdope.com/u/am77494)
#### Post date: [June 1, 2012, 9:33pm UTC](https://boards.straightdope.com/t/excel-question-dynamic-row-reference-within-a-formula/623815/2 "2012-06-01T21:33:34Z")
> [@Lance\_Steele](#):
>
> Is there a way to make the row number that a formula looks at itself the result of a formula? For example, I want to do a countif in a horizontal range of cells. I want the row number of that range to be the result of a vlookup. My coworker and I have been researching this all morning to no avail.
Make the formula look at VLOOKUP itself - number the rows 1 to … and then based on the result of your first VLOOKUP, do a second VLOOKUP on the rows
---
### Author:  [@Evil\_Economist](https://boards.straightdope.com/u/Evil_Economist)
#### Post date: [June 1, 2012, 10:12pm UTC](https://boards.straightdope.com/t/excel-question-dynamic-row-reference-within-a-formula/623815/3 "2012-06-01T22:12:14Z")
> [@Lance\_Steele](#):
>
> Is there a way to make the row number that a formula looks at itself the result of a formula? For example, I want to do a countif in a horizontal range of cells. I want the row number of that range to be the result of a vlookup. My coworker and I have been researching this all morning to no avail.
There’s a ROW function that will do what you want. Then use the INDIRECT function with the ROW function as an argument.
---
### Author:  [@Evil\_Economist](https://boards.straightdope.com/u/Evil_Economist)
#### Post date: [June 1, 2012, 10:20pm UTC](https://boards.straightdope.com/t/excel-question-dynamic-row-reference-within-a-formula/623815/4 "2012-06-01T22:20:11Z")
I should probably clarify. Suppose you want to know the last value in a list, and suppose the list is in column A. You can type the following: =INDIRECT(CONCATENATE(“A”,COUNTA(A:A))). now I realize that I’m not using the ROW function here, but you could…does this work?
---
### Author:  [@Evil\_Economist](https://boards.straightdope.com/u/Evil_Economist)
#### Post date: [June 1, 2012, 10:25pm UTC](https://boards.straightdope.com/t/excel-question-dynamic-row-reference-within-a-formula/623815/5 "2012-06-01T22:25:41Z")
> [@am77494](#):
>
> Make the formula look at VLOOKUP itself - number the rows 1 to … and then based on the result of your first VLOOKUP, do a second VLOOKUP on the rows
No! Too easy!
This is my third post in a row to indicate my awareness that I misunderstood the question. Sorry, late on a Friday. Give me a sec here..
---
### Author:  [@Lance\_Steele](https://boards.straightdope.com/u/Lance_Steele)
#### Post date: [June 1, 2012, 11:19pm UTC](https://boards.straightdope.com/t/excel-question-dynamic-row-reference-within-a-formula/623815/6 "2012-06-01T23:19:08Z")
> [@Evil\_Economist](#):
>
> Then use the INDIRECT function with the ROW function as an argument.
Yeah, that’s what we figured out. Basically I want to make sure it’s looking at the right row if our users filter or sort the main sheet.
Anyway, thanks for the input!