Excel Lookup Functions Cheat Sheet
Most people land here stuck. Start with the table that tells you which lookup function to use, copy the syntax you need, then check the error table at the bottom when Excel throws #N/A at you. VLOOKUP, XLOOKUP, INDEX-MATCH and HLOOKUP, all on one page.
Every Excel lookup function, plus the fix for every error
| Use this | When | Syntax |
|---|---|---|
| XLOOKUP | You are on Excel 365 or Excel 2021 and later. Exact match by default, it can look left, and it handles a missing value for you. This is the one to reach for first. | =XLOOKUP(lookup_value, lookup_array, return_array, "Not found") |
| VLOOKUP | A simple left to right lookup where the key already sits in the leftmost column of your range. Works in every version of Excel. | =VLOOKUP(lookup_value, table_array, col_index, FALSE) |
| INDEX and MATCH | No XLOOKUP in your version, or you need to return a value to the LEFT of the key, or you need to look up by row and column at the same time. | =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) |
| HLOOKUP | Your data runs across in rows instead of down in columns, so the key sits in the top row of the table. | =HLOOKUP(lookup_value, table_array, row_index, FALSE) |
| Argument | What it does | Worked example |
|---|---|---|
| lookup_value | The thing you are searching for, usually a cell reference such as E2. | =VLOOKUP(E2, A:C, 3, FALSE) |
| table_array | The range that holds your data. The lookup column must be the leftmost column of this range. VLOOKUP always searches column one and counts right from there. | =VLOOKUP(E2, A:C, 3, FALSE) |
| col_index | Which column to return, counted from the left edge of table_array. In A:C, a col_index of 3 returns column C. | =VLOOKUP(E2, A:C, 3, FALSE) |
| FALSE, exact match | Almost always what you want. Excel returns the exact row or gives you #N/A. A zero does the same job as FALSE. | =VLOOKUP("Widget A", A:C, 3, FALSE) |
| TRUE, approximate match | Only for sorted numeric bands such as tax brackets or grade cutoffs. It returns the closest value below your lookup value, and the list must be sorted smallest to largest. | =VLOOKUP(87, A:B, 2, TRUE) |
| Leaving it off | The trap. Excel defaults to TRUE, guesses an approximate match, and quietly hands back the wrong row with no error. Type FALSE every time. | =VLOOKUP(E2, A:C, 3) is a bug waiting to happen |
| Full guides | How to use VLOOKUP, VLOOKUP exact match, VLOOKUP from another sheet, VLOOKUP across multiple columns, Reverse VLOOKUP, VLOOKUP in Google Sheets, HLOOKUP | |
| Feature | Why it replaces VLOOKUP | Example |
|---|---|---|
| The syntax | Three required arguments, and an optional fourth for the not found message. No column counting anywhere. | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) |
| Exact match by default | There is no FALSE to forget, so the silent approximate match bug simply cannot happen. | =XLOOKUP(E2, A:A, C:C) |
| It can look left | The lookup range and the return range are separate arguments, so the key can sit anywhere in the sheet, including to the right of what you want back. | =XLOOKUP(E2, C:C, A:A) |
| No column counting | You point straight at the return column, so inserting or deleting a column in the middle of your table will not quietly break the formula. | =XLOOKUP(E2, A:A, D:D) |
| Built in if_not_found | The fourth argument replaces wrapping the whole thing in IFERROR. | =XLOOKUP(E2, A:A, C:C, "Not found") |
| Two criteria at once | Multiply the conditions together and look up the value 1. No helper column needed. | =XLOOKUP(1, (A:A=E2)*(B:B=F2), C:C) |
| Full guides | XLOOKUP vs VLOOKUP, XLOOKUP vs INDEX-MATCH, XLOOKUP with multiple criteria, XLOOKUP in Google Sheets | |
| Piece | What it does | Example |
|---|---|---|
| How the pair works | MATCH finds the position of your value in a list and returns a number. INDEX returns whatever sits at that position in another list. Together they do a lookup with no column counting. | =INDEX(C:C, MATCH(E2, A:A, 0)) |
| The 0 in MATCH | The third argument of MATCH. Zero means exact match. Leave it out and MATCH guesses, exactly like VLOOKUP does. | =MATCH(E2, A:A, 0) |
| It looks any direction | The return range is independent of the lookup range, so INDEX-MATCH happily returns a value to the left of the key. | =INDEX(A:A, MATCH(E2, C:C, 0)) |
| Two way lookup | Nest a second MATCH where the column number goes, and the formula lands on the cell where a row label and a column label intersect. | =INDEX(B2:E20, MATCH(G2, A2:A20, 0), MATCH(H2, B1:E1, 0)) |
| Why bother | It works in every version of Excel, it survives inserted columns, and it is still the fastest option on very large tables. | =INDEX(Prices, MATCH(SKU, Keys, 0)) |
| Full guides | INDEX and MATCH explained, The INDEX function, VLOOKUP vs INDEX-MATCH, INDEX-MATCH in Google Sheets | |
| Error | What it means | The fix |
|---|---|---|
| #N/A | The lookup value was not found. Usually a typo, a trailing space, a value that genuinely is not in the list, or a lookup range that does not actually contain the key column. | Clean both sides with TRIM, confirm the key really exists, and check your range starts at the key column. To show a friendly message instead, use =IFERROR(VLOOKUP(TRIM(E2), A:C, 3, FALSE), "Not found") or the fourth XLOOKUP argument. IFERROR guide |
| #REF! | Your col_index points past the right edge of table_array. Asking for column 4 inside a three column range is the classic case. Deleting a column that a formula pointed at does it too. | Lower the col_index or widen the range so the column actually exists. =VLOOKUP(E2, A:C, 3, FALSE), not 4. Better, switch to XLOOKUP so there is no index to get wrong. Fix #REF! |
| #VALUE! | An argument is the wrong type. In VLOOKUP it almost always means col_index is 0, negative, or text instead of a number. It can also mean a nested function was fed the wrong thing. | Make col_index a positive whole number, and check no argument has stray quotes around a number. Fix #VALUE! |
| #NAME? | Excel does not recognize the function name. Either it is misspelled, or you typed XLOOKUP in a version that does not have it, since XLOOKUP only exists in Excel 365 and 2021 and later. | Check the spelling, then check your version. If XLOOKUP is not available, use INDEX-MATCH instead. Fix #NAME? |
| Wrong answer, no error | The approximate match bug. You left off the last argument, so Excel defaulted to TRUE and returned the closest value below yours instead of the exact row. This is the most dangerous one because nothing looks broken. | End every exact lookup with FALSE or 0. =VLOOKUP(E2, A:C, 3, FALSE). Only use TRUE on a sorted numeric band like a tax bracket. VLOOKUP exact match |
| VLOOKUP cannot look left | The value you want sits to the LEFT of your key column, and VLOOKUP only ever counts to the right of the leftmost column. You get #N/A, #VALUE!, or the wrong column entirely. | Use =XLOOKUP(E2, C:C, A:A) or =INDEX(A:A, MATCH(E2, C:C, 0)). Both look in any direction. Reverse VLOOKUP |
| Text vs number mismatch | A key stored as text will never match the same key stored as a number, so the lookup silently fails with #N/A even though the value is clearly sitting right there. Numbers pasted from an export are the usual culprit. | Make both sides the same type. =VLOOKUP(VALUE(E2), A:C, 3, FALSE) when the table holds real numbers, or =VLOOKUP(TEXT(E2,"0"), A:C, 3, FALSE) when the table holds text. All Excel formula errors |
Get the printable PDF
We will email you the Excel Lookup Cheat Sheet as a printable PDF, yours to keep. No cost, and you can unsubscribe any time.
How to use this cheat sheet
Work top down. The first table tells you which function fits the job, so you are not fighting VLOOKUP when XLOOKUP would take ten seconds. Then copy the syntax block for that function, swap in your own ranges, and you are done.
When something breaks, jump straight to the errors table. Every error Excel throws at a lookup has one or two common causes, and the fix is sitting in the right hand column. Print the page or grab the PDF and keep it next to you while you work.
Go deeper
Excel Cheat Sheet
The full pillar. Every function family, shortcut, and formula worth knowing, in one place.
Open the Excel cheat sheetExcel Shortcuts Cheat Sheet
The keyboard shortcuts that actually save time, for Windows and Mac.
Open the shortcuts cheat sheetExcel Formulas Cheat Sheet
SUMIF, IF, TEXT, dates, and the rest of the formulas you reach for every week.
Open the formulas cheat sheetGoogle Sheets Formulas Cheat Sheet
The same lookups and formulas, written the Google Sheets way.
Open the Google Sheets cheat sheetQuestions people ask
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP is the modern replacement. VLOOKUP can only search the leftmost column of a range and count columns to the right, and it defaults to an approximate match if you forget the FALSE. XLOOKUP takes the lookup range and the return range as separate arguments, so it can look left as easily as right, it matches exactly by default, and it has a built in if_not_found argument so you do not need IFERROR. If you have Excel 365 or Excel 2021 and later, use XLOOKUP. If you are on an older version, VLOOKUP or INDEX-MATCH is the way.
Why does my VLOOKUP return #N/A?
Four causes cover almost every case. The lookup value genuinely is not in the first column of your table_array. There is a trailing space on one side, which you fix by wrapping the lookup value in TRIM. The key is stored as text on one side and as a number on the other, so it never matches even though it looks identical. Or your table_array does not start at the key column, because VLOOKUP only ever searches column one of the range you gave it. To show a friendly message instead of the error, wrap the formula in IFERROR.
Is INDEX-MATCH better than VLOOKUP?
For anything beyond the simplest lookup, yes. INDEX-MATCH can return a value to the left of your key, it does not break when someone inserts a column in the middle of the table, and it is faster on very large data sets. VLOOKUP is fine for a quick left to right lookup, and it is shorter to type. If you have XLOOKUP available, though, it gives you the flexibility of INDEX-MATCH with syntax that is easier to read.
Does this work in Google Sheets?
Yes. VLOOKUP, HLOOKUP, INDEX, and MATCH all work in Google Sheets with the same syntax you see here. XLOOKUP is supported in Google Sheets too. The main difference is that Google Sheets uses a comma or a semicolon as the argument separator depending on your locale, so if a formula throws an error, check your separators first.
Skip the syntax, start from a finished file
Simple Sheets has 100+ done for you Excel and Google Sheets templates with the lookups, dashboards, and formulas already built and tested.
Browse the template catalogMore free stuff: free calculators and tools, free Excel templates, and a free CRM template.
