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
There’s a ROW function that will do what you want. Then use the INDIRECT function with the ROW function as an argument.
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?
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..
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!