VLOOKUP practice in Google Sheets
VLOOKUP looks up a value in the first column of a table and returns a value from another column. Google Sheets calls the last argument is_sorted, but it works as in Excel: FALSE for an exact match, and TRUE or left out for the nearest smaller value in a sorted column.
How VLOOKUP works
VLOOKUP finds a value in the first column of a table and returns the value from another column in the same row. It takes four arguments: what to look for, the table, which column to return (counting from the table's first column), and FALSE for an exact match.
=VLOOKUP(A2,$E$2:$G$50,3,FALSE) looks up the ID in A2 in the first column of E2:G50 and returns the third column. The dollar signs keep the table in place when you copy the formula down.
Most VLOOKUP mistakes are one of three things: leaving out FALSE, so Excel returns the nearest match instead of an exact one; a lookup value that is not in the first column of the table; and IDs stored as text in one place and as numbers in the other. The exercises below cover a price lookup, a number key, approximate match for bands, filling prices into an order list with a locked table, and a lookup that fails on a stray space.
- 1Look up a price with VLOOKUPIn G2, use VLOOKUP to find the Price of the code in F2. Ask for an exact match.
- 2VLOOKUP a number keyPremiumIn G2, use VLOOKUP to return the Salary of the employee whose ID is in F2.
- 3VLOOKUP with an approximate matchPremiumIn E2, find the commission rate for the sales in D2. The table lists where each band starts, so use VLOOKUP with TRUE for an approximate match.
- 4Bring prices into an order listPremiumIn C2, look up the price of the code in B2 in the price table E2:F5. Lock the table with $ so the formula can be filled down the order list: VLOOKUP(B2,$E$2:$F$5,2,FALSE).
- 5Fix a VLOOKUP that fails on a spacePremiumThe code in F2 was pasted from an email and ends with a space, so a plain VLOOKUP returns #N/A. In G2, look up the price with TRIM(F2) as the lookup value.
Functions in this topic
Questions
Why does VLOOKUP return #N/A in Google Sheets?
The value is not in the first column of the range. Check for extra spaces, numbers stored as text and a missing FALSE at the end, just as in Excel.
Can VLOOKUP look to the left in Google Sheets?
No. VLOOKUP only returns columns to the right of the lookup column, as in Excel. Use XLOOKUP or INDEX and MATCH instead. XLOOKUP works in Google Sheets with the same arguments as in Excel, and its return range can sit on either side of the lookup range.