Row Number of VLOOKUP result
 

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

Row Number of VLOOKUP result
 

Hi,

Use MATCH

=MATCH(C1,A1:A10,0)

Where C1 contains your lookup value. Note this returns the position in the
range so if your range was a2 - A10 you would have to add 1 to the result to
get the correct row.

Mike

"Philip Mark Hunt" wrote:

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

Row Number of VLOOKUP result
 

Mike H wrote...
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.