The main difference between VLOOKUP and LOOKUP functions is the VLOOKUP is limited to vertical lookups only and the LOOKUP function has cross functionality which means that it can perform both vertical lookups and horizontal lookups. However, there are some other differences as well. We will step through some Examples below to express the differences.
LOOKUP is limited to Approximate match
- VLOOKUP syntax:
VLOOKUP(Lookup Value, Table Array, Column to Return, Approximate Match [True/False]) - LOOKUP syntax:
LOOKUP(Lookup Value, Lookup Array, Value to Return Array) - We can see that the LOOKUP function doesn’t have the True/False Option
- For this Example, setup your data so that Column A is the lookup array and Column B is the Returned Value array
- In cell E2 enter your lookup value
- In cell E3 type this formula “
=LOOKUP(E2,A3:A8,B3:B8)”
Figure 1. VLOOKUP vs. LOOKUP
We can see in this Example that the formula incorrectly selected “John” because of the default approximate match. Now lets create the same calculation using VLOOKUP
- Click in cell E3 and type this formula “
=VLOOKUP(E2,A3:B8,2,FALSE)”
Figure 2. VLOOKUP vs. LOOKUP
We can see from the VLOOKUP example that the formula returned an error because there was no exact match for “3” in the lookup array
LOOKUP is Cross-Functional
- In the previous LOOKUP Example, we identified the vertical lookup capabilities of the LOOKUP function. It can also perform a lookup horizontally
- For this example, we will take the same data and assume it is organized horizontally
- In ROW 1 enter the lookup array, enter the return array in ROW 2
- Type “4” in cell B4
- In cell B5 type this formula “
=LOOKUP(B4,B1:G1,B2:G2)”
Figure 3. VLOOKUP vs. LOOKUP
We can see in this example that the LOOKUP function returns the accurate information from the table even though it is organized horizontally
Leave a Comment