How to get the results of a VLOOKUP function search in cell address.(Microsoft Excel)
Here we will show you how to get the result of a search function not by value but by coordinates (cell address).
Steps
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.
- XLOOKUP Function
- INDEX and MATCH Functions
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 ))=CELL("address",XLOOKUP(E3,B3:B6,C3:C6))
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))=CELL("address",INDEX(C3:C6,MATCH(E3,B3:B6,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.
| Function | Result |
|---|---|
| VLOOKUP | Value of search result. |
| XLOOKUP INDEX + MATCH | 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.
---