--- title: "VLOOKUP in Excel — Syntax, Traps & Practice | SheetDo" url: https://stacklist.com/card/0d9fd799-4634-441a-a61a-c86b7d833347 source_url: "https://sheetdo.com/formulas/vlookup/?utm_source=stacklist&utm_medium=directory&utm_campaign=excel-accounting-student-stack" stack: https://stacklist.com/c/education/stack/ef471b61-81dc-4b55-ae0d-812c092b9c57 summary: "VLOOKUP is a vertical lookup formula in spreadsheets that searches the leftmost column of a table and returns a corresponding value from a designated column using four arguments. The function supports exact and approximate matching, wildcard partial matches, and requires proper cell reference locking to avoid errors when copying formulas." tags: "vlookup, spreadsheet, excel, google-sheets, lookup-formula, exact-match, approximate-match" key_entities: "VLOOKUP (technology), Excel (technology), Google Sheets (technology), SheetDo (organization), exact match (concept), approximate match (concept), wildcard matching (concept)" classification: "tutorial" content_hash: "sha256:e116a671b1f5944c3e3ea6459285e2b5ec72c349446c43af4be18fb21412b559" acp_version: "0.2" token_counts_approximate: 5428 visibility: public agent_accessible: true status: "final" --- # VLOOKUP in Excel — Syntax, Traps & Practice | SheetDo Home / Formulas / VLOOKUP function Lookup & Reference Excel + Google Sheets VLOOKUP function SheetDo · Updated November 4, 2026 VLOOKUP is the most famous lookup formula in spreadsheets — and the easiest to break. Learn the 4 arguments, how approximate match differs from exact match, how to avoid fragile column indexes, and test your skills live. VLOOKUP Syntax & The 4 Arguments =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) VLOOKUP (Vertical Lookup) scans the leftmost column of your table vertically, finds the match, and returns the corresponding value from the column you designate. 1 lookup_value Required The value to search for (e.g. SKU in E2, an employee name, or a number). This value MUST exist in the leftmost column of table_array. 2 table_array Required The range of cells containing your lookup data (e.g. A2:D6). Always lock this range with $ ($A$2:$D$6) when filling across rows or columns. 3 col_index_num Required The column number in the table_array to retrieve data from (starting at 1 for the first column). For A2:C6, column A is 1 and C is 3. 4 range_lookup Optional Match type: FALSE (or 0) for an exact match (essential for IDs, names, SKUs). TRUE (or 1, or omitted) for approximate match (tax tiers, graded ranges). Excel VLOOKUP example Column A lists product SKUs, column B lists names, and column C lists unit prices. Cell E2 holds the SKU to find: SKU-102. This Excel VLOOKUP example uses an exact match. SKU Product Unit price SKU-101 Notebook 12 SKU-102 Pen set 8 SKU-103 Binder 15 SKU-104 Stapler 9 =VLOOKUP(E2, A2:C5, 3, FALSE) VLOOKUP searches the first column of A2:C5 for SKU-102 and returns 8 from column 3 of the same row. FALSE (or 0) forces an exact match. Leave the fourth argument out and Excel VLOOKUP switches to approximate match, which can return the wrong row on unsorted data without raising an error. A missing SKU, a number stored as text, or a stray space returns #N/A. Exact match vs approximate match: TRUE or FALSE The fourth argument decides what happens on a miss. FALSE (or 0) demands an exact match and returns #N/A when nothing matches. TRUE (or 1, or leaving the argument blank) returns the closest value at or below the lookup value, and quietly requires the first column to be sorted ascending. Lower bound Rate 0 0% 10000 10% 30000 22% A 25000 lookup with approximate match lands on the 10% row =VLOOKUP(25000, $A$2:$B$4, 2, TRUE) The same lookup with exact match returns #N/A, because 25000 is not one of the bounds =VLOOKUP(25000, $A$2:$B$4, 2, FALSE) That contrast is the whole trap. A tax table wants approximate match, an employee ID wants exact match, and the argument that separates them is the one most people leave off. Type FALSE (or 0) unless the table is deliberately a set of thresholds. VLOOKUP partial match with wildcards When you know part of a value but not all of it, wrap the fragment in * and keep the fourth argument at FALSE. * stands for any run of characters, ? for exactly one character, and ~ escapes a literal * or ? . Product names in column A, prices in column B: return the price of the first name that contains the text in E2 =VLOOKUP("*"&E2&"*", $A$2:$B$5, 2, FALSE) Wildcards match text only. A cell holding the number 1001 will not match "*1001*", even though the two look identical on screen. Convert the column to text first, or join it with &"". The wildcard belongs in the lookup value, not in the table. The table itself stays untouched. Only the first match comes back. If several rows contain the fragment, sort the table so that the row you want comes first. For a case-sensitive or regular-expression search, FILTER and REGEXMATCH in Google Sheets are simpler than bending VLOOKUP. Why copying the formula down breaks the lookup (and the $ fix) The lookup range is relative by default, so it travels with the formula. Drag =VLOOKUP(E2, A2:C5, 3, FALSE) from row 2 to row 3 and Excel writes =VLOOKUP(E3, A3:C6, 3, FALSE) . The table now starts one row lower, and its first row has fallen out of the range. Filled into What gets written Result D2 =VLOOKUP(E2, A2:C5, 3, FALSE) finds SKU-101 to SKU-104 D3 =VLOOKUP(E3, A3:C6, 3, FALSE) SKU-101 has dropped out; it sits above the range D4 =VLOOKUP(E4, A4:C7, 3, FALSE) SKU-101 and SKU-102 are out D5 =VLOOKUP(E5, A5:C8, 3, FALSE) #N/A for any SKU but SKU-104 Locked once, safe for the whole column =VLOOKUP($E2, $A$2:$C$5, 3, FALSE) The two dollar signs do different jobs. $A$2:$C$5 freezes the table so every row reads the same block. $E2 freezes the column but not the row, which is what you want when the formula is filled sideways. Select a reference and press F4 to cycle through the four combinations. Looking up with more than one condition VLOOKUP takes a single lookup value, so two conditions need a single key. Add a helper column at the left edge of the table that joins the conditions, then build the same string inside the formula. Key (A) Branch Product Price Tokyo|Keyboard Tokyo Keyboard 199 Tokyo|Mouse Tokyo Mouse 89 Osaka|Keyboard Osaka Keyboard 209 Osaka|Mouse Osaka Mouse 95 Helper column in A2, filled down to A5 =B2&"|"&C2 With the branch in G2 and the product in H2 =VLOOKUP(G2&"|"&H2, $A$2:$D$5, 4, FALSE) The separator is doing real work. Without it, the pair Tokyo + Mouse and the pair Tok + yoMouse produce the same joined string, and the lookup silently returns the wrong row. Any character that cannot appear inside the values will do. VLOOKUP across sheets and workbooks Put the sheet name in front of the range. Quote the name only when it contains a space or a character Excel reserves. Another sheet in the same workbook =VLOOKUP($E2, Prices!$A$2:$C$500, 3, FALSE) A sheet whose name contains a space =VLOOKUP($E2, 'Price list 2026'!$A$2:$C$500, 3, FALSE) A reference into another workbook carries the full file path. If that file is closed or has moved, the formula shows its last saved value or #REF! until the source is opened again. Google Sheets cannot read a cell in a separate file directly. Pull the range with IMPORTRANGE first: =VLOOKUP(E2, IMPORTRANGE("sheet url", "Prices!A2:C500"), 3, FALSE) . Name the source range (Formulas → Define Name) and the formula shrinks to =VLOOKUP($E2, Prices, 3, FALSE) , which survives columns being inserted in the source. Fixing #N/A when the value is clearly in the table #N/A means no match was found. The value is usually there and simply not equal to what you are searching for. Work down this list in order: The value is not in the first column of the range. VLOOKUP scans that column and nothing else. Leading or trailing spaces. Strip them with =TRIM(E2) . A number stored as text. =VALUE(E2) coerces the lookup side, &"" coerces it the other way — pick the direction the table uses. Full-width characters, a non-breaking space pasted from a web page, or a character that looks like the one in the table but is a different Unicode code point. Case is not a cause: VLOOKUP ignores case. The fourth argument is missing, so approximate match is running against an unsorted table. The range shifted after the formula was copied down. See the section above. When the value really is not in the table, replace the error with a label a reader can act on: An unregistered code prints as Not found instead of #N/A =IFERROR(VLOOKUP(E2, $A$2:$C$6, 3, FALSE), "Not found") IFERROR also hides every other mistake, which is how a wrong column index survives for months. IFNA catches only #N/A. A column index larger than the range gives #REF!, one smaller than 1 gives #VALUE!. IFERROR vs IFNA→ Setting the sheet up for a long lookup A VLOOKUP down a few hundred rows only stays readable if the header row is frozen — otherwise you lose track of which column you are pulling. Freeze the header row while scrolling→ 4 Critical VLOOKUP Gotchas & Pitfalls 1. Approximate match by default (The silent disaster) If you omit range_lookup, VLOOKUP defaults to TRUE. On unsorted text data, it won't throw an error — it silently returns an incorrect row! Always explicitly type FALSE or 0 for exact lookups. 2. Left-to-right only (Cannot look backwards) The lookup value must reside in the first column of table_array. If your search key is in column C and you need data from column A, VLOOKUP cannot do it. Use INDEX+MATCH or XLOOKUP instead. 3. Hardcoded column index numbers break easily col_index_num is static (e.g. 3). If someone inserts a column into your table, VLOOKUP still reads column 3, returning wrong data or #REF! without any syntax warning. 4. #N/A from format mismatch (Text vs Number) An ID stored as text ('1001') will never match a numeric 1001. Trailing or leading spaces will also cause lookup misses. Wrap with TRIM() or VALUE() if data is dirty. Lookup Showdown: VLOOKUP vs XLOOKUP vs INDEX+MATCH VLOOKUP The classic workhorse. Universal compatibility across all legacy workbooks and the #1 most tested formula in job interviews. Drawbacks: left-to-right only and fragile column indexes. XLOOKUP The modern standard. Looks left or right, includes built-in not_found fallback, defaults to exact match, and doesn't break when columns are inserted. INDEX + MATCH The bulletproof veteran. =INDEX(A2:A6, MATCH(E2, C2:C6, 0)). Unaffected by column insertions, handles left lookups, and works in any version of Excel or Sheets. Excel vs Google Sheets Features Microsoft Excel (365 / Desktop) In Microsoft 365, VLOOKUP supports array constants to return multiple columns at once: =VLOOKUP(F2, A2:D6, {2, 3, 4}, FALSE). Legacy Excel requires separate column formulas. Google Sheets Google Sheets supports full-column expansion via =ARRAYFORMULA(VLOOKUP(E2:E, A2:D, 3, FALSE)), calculating thousands of rows from a single top formula. VLOOKUP Hands-on Practice: Look up Unit Price by SKU Type the formula directly into the locked spreadsheet below for millisecond feedback, or open the standalone full-screen challenge. Open full challenge Description Hints 0/4 Solutions 2 – Test case 1 2 3 Reset Submit fx A B C D E F G 1 2 3 4 5 6 7 8 9 10 Loading interactive spreadsheet... Preparing grid cells and formula calculation engine ○ Ready Enter formula in highlighted cells, then click Submit. Description Hints 0/4 Solutions 2 – Test case 1 2 3 Reset Submit fx A B C D E F G 1 2 3 4 5 6 7 8 9 10 Loading interactive spreadsheet... Preparing grid cells and formula calculation engine ○ Ready Enter formula in highlighted cells, then click Submit. Recommended VLOOKUP Challenges Master exact match, approximate tax tiers, multi-column retrieval, and IFERROR fallback handling in real business scenarios. Easy Lookup & Reference Look up salary by employee ID (XLOOKUP) You are putting together a department salary report. Employee IDs and departments are already filled in, but the Matched salary column is still empty. Write a formula in G2:G6 so each row looks up the salary for the employee ID in column E from the staff roster in A2:C6 . Easy Logic Unit prices that survive bad data (IFERROR) An order's unit price is Total divided by Qty — until someone types 0 as the quantity and every division in the report turns into #DIV/0!. Build the safe version in Unit price cell D2 : show the normal price when the math works, and the text "Check qty" when the division blows up. Medium Logic Audit missing SKUs with ISNA Before the price report ships, QA wants a simple audit answer for the SKU in E2 : is it missing from the price list in A2:A6 or not? Put TRUE (missing) or FALSE (present) in the Missing? cell D2 . This is an audit question — you want the TRUE/FALSE itself, not a wrapped-up message. Medium Math Average deal size for one region (AVERAGEIF) The sales deck quotes an average deal size per region. The pipeline is in A2:C7 and the region to report is in E2 . Put the average deal size for that region in the Avg deal cell F2 , and 0 when the region has no deals at all. Medium Lookup & Reference Default to zero on missing discount (VLOOKUP + IFERROR) You are processing store checkouts. Members get a discount based on their VIP tier in A2:B5 (Bronze 5%, Silver 10%, Gold 15%, Platinum 20%). Non-VIP customers have standard tiers like 'Regular' or 'None' that are NOT in the VIP table, causing a naked VLOOKUP to fail with #N/A . Fill the Discount cells E2:E5 using VLOOKUP wrapped in IFERROR (or IFNA) so missing tiers safely default to 0 . Medium Lookup & Reference Find tax bracket using approximate match (VLOOKUP) HR needs to compute the withholding tax rate for each employee's annual taxable income. The reference table in columns A–B lists income thresholds in ascending order (0, $30,000, $60,000, $100,000) and their corresponding tax rates. Fill the Tax rate cells F2:F4 using VLOOKUP with approximate match so each employee is assigned the rate matching their income tier in E2:E4 . FAQ How does VLOOKUP work in Excel and Google Sheets? =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches the first column of table_array for lookup_value, then returns the value in the same row from col_index_num. Why does VLOOKUP return #N/A? 1) The lookup value does not exist in the table's first column. 2) Leading or trailing spaces in the text. 3) Type mismatch (numbers stored as text vs real numbers). 4) Forgetting FALSE as the 4th argument on unsorted data. What is the difference between TRUE and FALSE in VLOOKUP? FALSE (or 0) requires an exact match. TRUE (or 1, or omitted) uses approximate match, requiring the lookup table to be sorted ascending to find the closest smaller threshold (like tax brackets). How do I do a VLOOKUP in Google Sheets across an entire column? Google Sheets supports =ARRAYFORMULA(VLOOKUP(E2:E, A2:C, 3, FALSE)) which calculates for every row automatically without dragging down. How do I do a VLOOKUP with two conditions? VLOOKUP itself takes one lookup value. Join the conditions in a helper column at the left edge of the table (=B2&"|"&C2) and join them the same way in the formula: =VLOOKUP(G2&"|"&H2, $A$2:$D$5, 4, FALSE). XLOOKUP in Microsoft 365 can take the two conditions directly as boolean arrays. Can VLOOKUP find a partial match? Yes. Wrap the fragment in wildcards and keep the fourth argument at FALSE: =VLOOKUP("*"&E2&"*", $A$2:$B$5, 2, FALSE). Wildcards match text only, so a numeric ID stored as a number will not match. Only the first matching row is returned. How do I look up a value on another sheet? Prefix the range with the sheet name: =VLOOKUP($E2, Prices!$A$2:$C$500, 3, FALSE). Quote the name when it contains a space ('Price list 2026'!$A$2:$C$500). Google Sheets needs IMPORTRANGE when the source is a different file. Should I use XLOOKUP or VLOOKUP? If your team uses modern Excel (Microsoft 365) or Google Sheets, XLOOKUP is safer: it looks in any direction, doesn't break when columns are inserted, and defaults to exact match. VLOOKUP remains essential for legacy workbooks (.xls / .xlsx) and interview tests.