Describe the lookup in plain fields and get the finished formula, for Excel, Google Sheets and the modern XLOOKUP version.
Free, no signup, nothing to download. Results update as you type.
=VLOOKUP(A2, Sheet2!A:D, 3, FALSE)Looks up the value in A2 down the FIRST column of Sheet2!A:D and returns whatever sits in the third column of that range. FALSE means exact match, and it is what you want almost every time.
VLOOKUP(lookup value, table range, column number, FALSE) searches the FIRST column of the range for the lookup value and returns the value in the numbered column, counting the first column as 1. FALSE means exact match. Wrapping it in IFERROR replaces the #N/A with your own text.
Four causes, in order of how often they are the real one: the lookup value is not in the FIRST column of the range, one side has trailing spaces or is text where the other is a number, the range is not locked so it drifted when filled down, and genuinely no match. Check the first column before anything else.
Almost always approximate match. With TRUE as the last argument and an unsorted first column, VLOOKUP returns the nearest smaller value rather than failing, so the wrong number lands in the report with no warning. Use FALSE unless you are deliberately banding numbers.
Yes, if you are filling the formula down. Sheet2!A:D is safe because whole columns do not shift, but Sheet2!A2:D500 will drift to A3:D501 on the next row. Write it as $A$2:$D$500.
XLOOKUP where you have it: it looks left as well as right, takes a not-found argument without IFERROR, and does not break when someone inserts a column. VLOOKUP remains the safe choice for files shared with older Excel versions.
Cross-sheet references by name rather than by column position, so [Products]![Price] does not break when a column moves. That fragility, counting columns, is the reason VLOOKUP generates this much search traffic in the first place.
The same numbers, live off the rows your team already edits, in a spreadsheet that rolls them up for you. Start your 7-day free trial, no credit card required.