Google Spreadsheet Check From What Cell VLOOKUP() Returns

user2706406 picture user2706406 · Aug 22, 2013 · Viewed 8.6k times · Source

Is there a way to return the cell from which VLOOKUP() gets its value?

For instance:

________A_____|__B__|
1 | Mouse     |  1  |
2 | Keyboard  |  2  |
3 | Headset   |  3  |
4 | HDD       |  4  |

=VLOOKUP("Mouse",A1:B4,2,FALSE) --would return "1".

Is there a way to make it return "B1" instead?

Answer

Vasim picture Vasim · Aug 22, 2013
=ADDRESS(MATCH("Mouse",A1:A4,0),2, 1)

How this works... The address part captures the cell address and is as follows

=Address(row,column,abs)

The match function returns Row number, 2 is the second coloumn - you will have to change as per your requirement. The abs (absolute) can be 1,2,3,4 or omitted....try changing it to understand....