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?
=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....