Google Spreadsheet Check From What Cell VLOOKUP() Returns

google-drive-api google-sheets spreadsheet gs-vlookup
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....

Related questions

How to automatically import data from uploaded CSV or XLS file into Google Sheets

I have a legacy database system (not web accessible) on a server which generates CSV or XLS reports to a Google Drive folder. Currently, I am manually opening those files in Drive web interface and converting them to Google Sheets. …

Read More
Google Sheets: How to replace text in column header?

I have a query like this =QUERY(B2:C9; "select (C * 100 / B) - 100") in my Google Sheets. What is displayed as a column header is: difference(quotient(product(100.0()))100.0()). I want to put a human readable description there instead. How can …

Read More
How can I create a hyperlink in the middle of cell text?

In google spreadsheets, it is possible to add a hyperlink to a cell as: =hyperlink("www.google.com", "search the web") This will show the text search the web which will hyperlink to google. However, I am wondering: How can …

Read More