VLOOKUP Formula: Ask AI to Merge Two Excel Tables
When this happens
Your order sheet has product codes and quantities, but the prices live on a separate sheet called 'Prices'. Looking up each price by hand would take forever. Describe both tables and where you want the result, and the AI will give you a formula that connects them.
Watch the video
Ask like this
Excel: Sheet1 has product codes in A and quantities in B. The 'Prices' sheet has codes in A and prices in B. Give me one formula for column C that pulls the matching price, and how to fill it down.
The answer we got
=VLOOKUP(A2,Prices!A:B,2,FALSE)It looks for the code in A2 in column A of the 'Prices' sheet and returns the price from the 2nd column (B) of that row. FALSE means it only accepts an exact match.
Select C2 and double-click the small square at its bottom-right corner (the fill handle) to copy the formula down. If a code isn't on the Prices sheet, you'll see #N/A, so check that code for typos or extra spaces.| 1 | A Code | B Qty | C Price (VLOOKUP) |
|---|---|---|---|
| 2 | P-102 | 3 | $20 |
| 3 | P-101 | 5 | $15 |
| 4 | P-103 | 2 | $30 |
| 5 | P-102 | 1 | $20 |
โ Put the formula in C2 and fill it down to get each price. Add =B2*C2 in column D for the line totals, and the 4 orders come to $215.
Tips for better results
- On a newer Excel? Try XLOOKUP
Microsoft 365 and Excel 2021 or later have XLOOKUP. You pick the lookup column and the return column separately, and you can set what to show when nothing matches.
=XLOOKUP(A2,Prices!A:A,Prices!B:B,"Not found") - Show a blank instead of #N/A
If some codes aren't on the price list yet, wrap the formula in IFERROR to show a blank instead of #N/A. Just make sure you still check for the missing codes.
=IFERROR(VLOOKUP(A2,Prices!A:B,2,FALSE),"") - Keep going with line totals
Once the prices are in, multiply quantity by price in the next column. You can also just ask the AI to "add a total column too."
=B2*C2
Today's checklist
- โ Name both sheets and their columns
- โกSay which cell gets the result
- โขAsk how to fill it down
FAQ
The code is definitely there, but I get #N/A.
Usually there's a trailing space, or one side is stored as a number and the other as text. Compare the two cells with a formula like =A2=Prices!A2, or ask the AI: "I get #N/A. Walk me through the likely causes in order."
Is the formula the same in Google Sheets?
Yes. VLOOKUP works the same way in Google Sheets. If the sheet name has a space, wrap it in single quotes, like 'Price list'!A:B.
What if the column I search is to the right of the price?
VLOOKUP needs the lookup column to be the leftmost column of the range. If you can't reorder the columns, use XLOOKUP or INDEX and MATCH. Describe your layout and ask the AI for that version.
More in this category
AI answers can vary by service and version. Double-check anything important before you use it.
Works in ChatGPT, Claude or Gemini.