Google Spreadsheet Check From What Cell VLOOKUP() Returns

Question

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?

score 17 · Accepted Answer · answered Aug 22 '13 at 10:00

17

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

answered Aug 22 '13 at 10:00

Vasim

3,052
3
35
56

I had to replace the commas(',') with semicolons(';') to get the formula work in google sheets. – Sheikh Faisal Miskhat Aug 07 '19 at 09:25
1

@SheikhFaisalMiskhat this is not true. It depends on the local settings of your spreadsheet! – marcomow Jan 08 '20 at 10:14
@marcomow true. based on my local settings it worked for me with the ';'. – Sheikh Faisal Miskhat Jan 23 '20 at 07:50

Google Spreadsheet Check From What Cell VLOOKUP() Returns

1 Answers1