How to get the results of a VLOOKUP function search in cell address.(Microsoft Excel)

Japanese version.

Here we will show you how to get the result of a search function not by value but by coordinates (cell address).


Many people search to find a way to get an address with the VLOOKUP function, but it is not possible with the VLOOKUP function.

Instead, it can be achieved by any of the following.

Search with either function and get the cell address with the CELL function.

XLOOKUP Function

The formula is concise.

If the XLOOKUP function is available, use this formula to achieve this.

=CELL("address",XLOOKUP(Search value, Search cell range, Result range ))

INDEX and MATCH Functions

If the XLOOKUP function is not available, this is the formula.

=CELL("address",INDEX(Result range,MATCH(Search value,Search cell range,0),0))

Note: Reason why it is not possible with VLOOKUP.

The VLOOKUP, XLOOKUP, and INDEX + MATCH functions do similar things, but differ in the way they return results.

VLOOKUPValue of search result.
Cell Reference.

Therefore, only functions whose results are cell references can retrieve cell coordinates with the CELL function.

This difference can be seen by inspecting with the ISREF function.



Tips. XLOOKUP & VLOOKUP & HLOOKUP(Microsoft Excel)