In school, retail stores and other places, multiple details are stored and it is a day-to-day task of fetching detailed information about a product or thing by just giving its code number or ID. This task can be executed easily using VLOOKUP function. In order to perform a two-way lookup using VLOOKUP in a table, MATCH function is used along with VLOOKUP function.
Two-way lookup using VLOOKUP in a Table
As the name suggests, the VLOOKUP performs a vertical search in the first column and returns the value in the row. On the other hand, MATCH function is also used for searching but it returns the relative position of the searched value. A two-way search is accomplished using both VLOOKUP and MATCH together.
The general formula to perform the two-way lookup using VLOOKUP in a table is:
Following required arguments are needed to add in this formula:
- Lookup_value1: This lookup value is to be searched vertically in a table.
- Table: It is table array containing rows and columns
- Lookup_value2: This is lookup value to be searched horizontally in the table header
- Table[#Headers]: This is an array or cell range containing table header fields
Basically, the syntax of above formula represents VLOOKUP function to perform a two-way lookup using VLOOKUP in a table. The only addition is MATCH function which basically replaces the col_index_number argument of VLOOKUP function to return the column index number where the lookup_value2 argument matches with table header (Table[#Headers] array.
In the example shown, we take a table containing details such as ID, First name, Last Name, Class and Section. If we wish to extract details of any student from his/her ID, then we can do so by simply entering the ID number and use VLOOKUP with MATCH to extract all the other values such as First name, Last Name, Class and Section from the table.
We provided ID number 888 in I4 as shown. Now, to get the first name in I5, we use the formula
=VLOOKUP($I$4, Table1, MATCH(H5,Table1[#Headers],0)0)
- Table1 is a named range from cell range B5:F20
- Table1[#Headers] is a named range from cell range C4:F4
Figure 1. Use of VLOOKUP in cell I5
The formula returns “John” in I4 which is the first name of the student with ID 888 by using two-way lookup using VLOOKUP in a table. Similarly, on dragging the formula to other cells, we get other values as needed.
Figure 2. Result returned by VLOOKUP in I5
We use dollar sign “$” with I4 cell as we want to lock this cell address so that while dragging the formula, this cell remains the same as all values correspond to ID no 888 and in this case, it is mentioned in cell I4. In this way, we can find the details of any student by entering his ID number.