Row Number of VLOOKUP result

[Philip Mark Hunt]

I am wanting to know the row number in which a VLOOKUP result was found. I
know I could have a column containing the row number, and return that as my
result, but that could lead to a good bit of other editing if I add such a
column to an already fairly well developed spreadsheet; I would just like to
avoid that option if I can.

Please offer help in either function calls and/or VBA macro - I am
comfortable with either or.

I would cross post in Programming too, but Daniel has told us that this
frowned on, and I don't know the USENET designation anyway - I wonder why
they even offer the option, when it is so difficult to find the necessary
information to do so, eh.

Best regards

Philip Hunt

 

[Harlan Grove]

Use MATCH

=MATCH(C1,A1:A10,0)

....

Generalizing, if the OP's VLOOKUP call were of the form

=VLOOKUP(v,t,c)

the row number of the match would be given by

=MATCH(v,INDEX(t,0,1))+MIN(ROW(t))+1

and if the OP's VLOOKUP call were of the form

=VLOOKUP(v,t,c,0)

the row number of the match would be given by

=MATCH(v,INDEX(t,0,1),0)+MIN(ROW(t))+1

These formulas work no matter in which cell the VLOOKUP table t begins.