vlookup function to return the cell address of the found item

  • Thread starter Thread starter skiing
  • Start date Start date
S

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,D1:D3000,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
 
You can return the (relative) row number using MATCH, like this:

=if(iserror(vlookup(A1,D1:D3000,1,false)),"not in
list",MATCH(A1,D1:D3000,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
 
Back
Top