vlookup function to return the cell address of the found item

[skiing]

I have a one column list of data (around 3,000 items) - and I am using
the vlookup function to determine if an item is in that list using
something like the formula below:

=if(iserror(vlookup(A1,D13000,1,false)),"not in list","in list")

I would like to know if I can have this function return the cell
address or row number to indicate the location of the item in the list
- is this possible?

Thank you for your time and assistance

 

[Pete_UK]

You can return the (relative) row number using MATCH, like this:

=if(iserror(vlookup(A1,D13000,1,false)),"not in
list",MATCH(A1,D13000,0))

As your range starts with D1, the relative row is the same as the actual
row, but if your range started with D10, for example, you would have to add
9 on to the result to get the actual row.

Hope this helps.

Pete

 

[Don Guillett]

Look in the help index for MATCH