Sometimes it happens, that we need to look up for a value from a table according to a row and column heading. In this situation, we can use a two-way lookup with VLOOKUP.
A two-way lookup formula enables us to cross-check two fields of information in a table by using a value of a column and a value of in a row. The best way to do this in Excel is to use VLOOKUP function together with a MATCH function.
In Excel, the VLOOKUP function works together with a MATCH to perform the two-way lookup. As we know that a normal VLOOKUP function only returns a value by looking into one column at a time. The reason is that the column_index parameter of a VLOOKUP is a static value, so if you combine a match function with a VLOOKUP function you can make it dynamic.
Two-way lookup with VLOOKUP
First, let us take a look at the ‘MATCH’ function whose generic parameter which is a col_name that denotes the name of the column and the second generic parameter the col_header which represents the range of the cell to be matched.
The VLOOKUP function consists of a generic parameter lookup_value which is value to be tested or checked for, while table parameter provides the range or array of cells which are to be looked upon.
Let us understand the two-way lookup with a VLOOKUP by considering an example. Let’s say that your teacher wants to check the marks scored by James in the subject Science as shown in the table given below. Two separate tables become one which is the table to lookup and the other which contains the details of what to search.
Figure 1. Lookup table array along with the search list
The subject to be looked on is science and the table range is entered as shown.
Figure 2. VLOOKUP function used for a two-way lookup
The MATCH function used here returns the position of the name (James). Note that that range specified also includes the empty cell. This is done so that the number which MATCH function returns is in sync with the table used by VLOOKUP. The ‘0’ for a match denotes exact match.
Figure 3. VLOOKUP function used along with MATCH function
The Marks of James are successfully located from the lookup table.
Figure 4. The result obtained for a two-way lookup