SkillNavoraData Science & AI Handbook
Advanced Excel · Lesson 7 of 12

Lookup Functions: VLOOKUP, XLOOKUP and INDEX-MATCH

Bring data together from different tables. Master VLOOKUP and HLOOKUP (exact and approximate match), the modern XLOOKUP, the flexible INDEX + MATCH combination (including two-way and reverse lookups), OFFSET, consolidating several sheets, CHOOSE, and how to troubleshoot

● Live classes 4–5✦ Excel for Microsoft 365, 2026Intermediate⏱ 120 min
⬇ Download PDF⬇ Practice workbook (.xlsx)
By the end of this lesson you can
  • Explain why lookups are the Excel equivalent of a SQL join
  • Use VLOOKUP and HLOOKUP with exact and approximate match, and know their limits
  • Use XLOOKUP for left lookups, default values, last-match search and multiple return columns
  • Combine INDEX and MATCH for reverse (left) and two-way (matrix) lookups
  • Use CHOOSE, and fix the common causes of
Datasets used

Why lookups matter

Business data is split across tables: orders in one, products in another, customers in a third. A lookup fetches a value from one table using a key that both tables share, such as a product ID or employee ID.

A lookup takes a key from one table, finds it in another table, and returns a value from the same row.
A lookup takes a key from one table, finds it in another table, and returns a value from the same row.
TipYou are already learning SQL joins

A lookup is a left join for one column at a time: keep every row of the main table and bring in matching information from the other table. You will see the same idea as JOIN in SQL (SQL Lesson 8), merge() in pandas, and relationships in Power BI.

We will use this lookup table on a sheet called Prod:

XProd: product lookup table (A1:E7)
ABCDE
1Product IDProductCategoryPriceStock
2P101Office ChairFurniture450032
3P102Standing DeskFurniture185008
4P103Laser PrinterTechnology1290015
5P104Notebook PackOffice Supplies240540
6P105SmartphoneTechnology2199927
7P106BookcaseFurniture76000

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value: what you are looking for (the key).
  • table_array: the table to search; the key must be in its first column.
  • col_index_num: which column of that table to return (1 = first column).
  • range_lookup: FALSE (or 0) for an exact match. Use TRUE only for approximate matching on sorted bands.
XOrder lines looking up product details with VLOOKUP
ABCDEF
1OrderProduct IDQtyProduct =VLOOKUP(B2,Prod!$A$2:$E$7,2,FALSE)PriceAmount
21001P1032Laser Printer12,90025,800
31002P1016Office Chair4,50027,000
41003P1051Smartphone21,99921,999
51004P10420Notebook Pack2404,800
61005P1093#N/A#N/A#N/A
  • D2=VLOOKUP(B2,Prod!$A$2:$E$7,2,FALSE)
  • F2=C2*E2
  • Each pattern is copied down/across to the other cells in its column.

Row 6 shows #N/A because P109 does not exist in the product table. That is the correct answer: the key is not there.

Common mistakesThe four classic VLOOKUP mistakes
  1. Forgetting FALSE: the default is approximate match, which silently returns a wrong row for unsorted data.
  2. Not locking the table: A2:E7 copied down becomes A3:E8, so the last rows are missed. Use $A$2:$E$7 or a Table name.
  3. Hard-coded column numbers: inserting a column inside the table changes which column 4 means. Use MATCH for the column number, or XLOOKUP.
  4. Key not in the first column: VLOOKUP cannot look to the left (use INDEX + MATCH or XLOOKUP).

Approximate match: bands and slabs

With TRUE, VLOOKUP finds the largest value less than or equal to the lookup value. The first column must be sorted ascending. This is perfect for tax slabs, commission tiers, discount bands and grades.

XApproximate match for commission tiers
ABCDEFG
1Sales fromCommissionRepSalesRateCommission
200%Asha85,0000%0
31000003%Ravi240,0003%7,200
42500005%Meera250,0005%12,500
55000007%Kabir610,0007%42,700
6100000010%Zoya1,200,00010%120,000

Ravi's 2,40,000 falls between 1,00,000 and 2,50,000, so he gets the 1,00,000 row: 3%. Meera's 2,50,000 matches the 2,50,000 row exactly: 5%.

HLOOKUP

HLOOKUP is VLOOKUP turned sideways: it searches the first row and returns a value from a row below. Use it when data runs across columns (for example months as headers).

XHLOOKUP across a horizontal table
ABCDEFGH
1MonthJanFebMarAprFind monthMar
2Target120130145150Target →145
3Actual118136139162Actual →139

XLOOKUP: the modern lookup (Microsoft 365 / Excel 2021+)

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP fixes every VLOOKUP limitation:

Feature VLOOKUP XLOOKUP
Default match Approximate (risky) Exact
Look to the left No Yes: lookup and return arrays are separate
Column inserted in the table Breaks the index number Safe
Custom "not found" message Needs IFERROR Built-in 4th argument
Return several columns One at a time Spills many columns
Search last to first No search_mode = -1
Wildcards Yes match_mode = 2
Next smaller / larger match Sorted data only match_mode = -1 or 1, any order
XXLOOKUP examples (lookup table on sheet Prod)
ABC
1NeedFormulaResult
2Price of P105=XLOOKUP("P105",Prod!A2:A7,Prod!D2:D7)21,999
3ID of "Bookcase" (left lookup)=XLOOKUP("Bookcase",Prod!B2:B7,Prod!A2:A7)P106
4Missing ID with a message=XLOOKUP("P109",Prod!A2:A7,Prod!B2:B7,"Not found")Not found
5Product, category and price at once=XLOOKUP("P103",Prod!A2:A7,Prod!B2:D7)Laser Printer
6Last Furniture item in the list=XLOOKUP("Furniture",Prod!C2:C7,Prod!B2:B7,,0,-1)Bookcase
7Product name containing "Desk"=XLOOKUP("*Desk*",Prod!B2:B7,Prod!D2:D7,,2)18,500

The fourth row returns three columns at once: the result spills across C5:E5.

XLOOKUP for bands (no sorting needed)

match_mode = -1 returns the exact match or the next smaller item, like VLOOKUP's TRUE, but the table does not need to be sorted:

=XLOOKUP(E2, $A$2:$A$6, $B$2:$B$6, , -1) gives the same commission rates as the approximate VLOOKUP above.

TipXMATCH

XMATCH is the modern version of MATCH with the same match and search modes as XLOOKUP. =XMATCH("P105",Prod!A2:A7) → 5.

INDEX and MATCH

Before XLOOKUP, professionals used INDEX + MATCH, and you will still see it everywhere. It works in every Excel version and is very flexible.

XINDEX returns a value at a position; MATCH returns the position of a value
ABCDEF
1StateCityPopulation 2011 (mn)FormulaResult
2MaharashtraMumbai18.4=INDEX(A2:C6,2,1)Delhi
3DelhiDelhi16.3=MATCH("Ahmedabad",B2:B6,0)3
4GujaratAhmedabad6.4=MATCH("Population 2011 (mn)",A1:C1,0)3
5West BengalKolkata14.1=INDEX(C2:C6,MATCH("Kolkata",B2:B6,0))14.1
6KarnatakaBengaluru8.5=INDEX(A2:A6,MATCH("Ahmedabad",B2:B6,0))Gujarat

Reverse (left) lookup

In the table above, City is in column B and State is in column A. VLOOKUP cannot return a column to the left of the key, but INDEX + MATCH can: =INDEX(A2:A6, MATCH("Ahmedabad", B2:B6, 0)) → Gujarat.

INDEX + MATCH: MATCH finds the row number of the city; INDEX returns the state on that row.
INDEX + MATCH: MATCH finds the row number of the city; INDEX returns the state on that row.

Two-way (matrix) lookup

INDEX can take a row and a column number. Use one MATCH for each to look up the intersection of a row and a column, for example an employee's salary from a table where both the employee and the field are chosen in drop-downs.

XTwo-way lookup: =INDEX($B$2:$E$6, MATCH($G3,$A$2:$A$6,0), MATCH(H$2,$B$1:$E$1,0))
ABCDEFGHI
1EmpNameDepartmentIDJobTitleSalaryCity
2Employee_1D10Developer82000PuneEmployeeJobTitleSalary
3Employee_2D20Analyst68000DelhiEmployee_4Manager125000
4Employee_3D10Tester54000PuneEmployee_2Analyst68000
5Employee_4D30Manager125000Mumbai
6Employee_5D20Developer79000Delhi

Because the headers in row 2 (JobTitle, Salary) are matched by MATCH, you can change a header to City and the formula returns cities without editing it. The XLOOKUP version nests one XLOOKUP inside another: =XLOOKUP($G3,$A$2:$A$6,XLOOKUP(H$2,$B$1:$E$1,$B$2:$E$6)).

INDEX + MATCH with multiple conditions

To match on two columns at once (for example Region and Product), multiply the conditions inside MATCH:

=INDEX(F2:F9, MATCH(1, (B2:B9="North")*(C2:C9="Tablet"), 0)) → returns the revenue for the North + Tablet row. In Microsoft 365 this works as a normal formula; in older Excel press Ctrl + Shift + Enter. With XLOOKUP: =XLOOKUP(1,(B2:B9="North")*(C2:C9="Tablet"),F2:F9).

OFFSET and consolidating worksheets

In class we also used lookups to consolidate data: pull values from several tables or sheets into one summary. Three tools do this job.

OFFSET: a value or range by position

=OFFSET(reference, rows, cols, [height], [width]) starts at reference, moves down rows and right cols, and returns that cell (or a range of height × width). Combined with MATCH it does the same two-way lookup as INDEX + MATCH. The table below holds month-end share prices (illustrative numbers for fictional companies).

XTwo-way lookup of a share price with INDEX + MATCH and with OFFSET + MATCH
ABCDEFGHIJ
1DateAsha FoodsKiran MotorsMeru SteelCompanyDateINDEX + MATCHOFFSET + MATCH
231-01-2026412118096Kiran Motors31-03-202612621262
328-02-20263981215101Meru Steel31-05-2026112112
431-03-2026425126299Asha Foods28-02-2026398398
530-04-20264401240108Last 3 months avg (Asha)433.67
631-05-20264361298112

The last formula shows OFFSET's real strength: OFFSET($B$1, COUNT(B2:B6)-2, 0, 3, 1) is a range of the last three prices, so the average updates automatically when a new month is added. The same idea builds dynamic named ranges and charts (Lesson 9).

Common mistakesOFFSET is volatile

OFFSET (like INDIRECT, TODAY and RAND) recalculates every time anything in the workbook changes. A few hundred OFFSETs are fine; tens of thousands slow a workbook down. For lookups prefer INDEX + MATCH or XLOOKUP, and for growing ranges prefer an Excel Table.

Consolidating several sheets into one

Situation Best tool How
Same layout on every sheet (Jan, Feb, Mar…) and you need totals 3-D reference =SUM(Jan:Mar!B2) adds cell B2 on every sheet from Jan to Mar (Lesson 4)
Same kind of data, but rows in a different order on each sheet Data → Consolidate Choose Sum, add each sheet's range, tick Top row and Left column, optionally Create links to source data
Different tables that share a key (city code, product ID) Lookups Bring each column in with XLOOKUP or INDEX + MATCH, as in the class tasks below
Many files every month Power Query Get Data → From Folder, then Append (Lesson 12)

Class practice: city tables

These are the consolidation tasks from the live class. Table 1 maps cities to states and population; Table 2 maps city codes to cities.

XConsolidation tasks: state from city, population from city code, grade from marks
ABCDEFGH
1City CodeCityStatePopulation 2011 (mn)TaskInputAnswer
2C1MumbaiMaharashtra12.48State of this cityPuneMaharashtra
3C2DelhiDelhi11.01Population of city codeC311.11
4C3AhmedabadGujarat5.57Population of city codeC44.47
5C4SuratGujarat4.47City code of this cityAllahabadC31
6C31AllahabadUttar Pradesh1.11
7C21PuneMaharashtra3.12MarksGrade (approx. match)
80D
958C
1091A
11Min marksGrade
120D
1340C
1460B
1580A

The state and city-code answers use INDEX + MATCH wrapped in IFNA; =XLOOKUP(G2,B2:B7,C2:C7,"Not found") gives the same result in Microsoft 365. The grade lookup uses approximate match (TRUE): VLOOKUP finds the largest "Min marks" that is less than or equal to the marks, so the grade table must be sorted ascending. This is the classic use of approximate match: tax slabs, commission bands and grades.

CHOOSE, and VLOOKUP with CHOOSE

=CHOOSE(index_num, value1, value2, …) returns the item at a position.

XCHOOSE examples
ABC
1InputFormulaResult
22=CHOOSE(A2,"Bronze","Silver","Gold")Silver
327-04-2026=CHOOSE(WEEKDAY(A3,2),"Mon","Tue","Wed","Thu","Fri","Sat","Sun")Mon
415-08-2026="Q"&CHOOSE(MONTH(A4),4,4,4,1,1,1,2,2,2,3,3,3)Q2

The last example gives the Indian financial-year quarter (April–June = Q1), which a plain ROUNDUP(MONTH()/3) cannot do.

VLOOKUP + CHOOSE was the old trick for a left lookup: CHOOSE({1,2}, B2:B6, A2:A6) builds a virtual two-column table with City first and State second, so VLOOKUP can search it:

=VLOOKUP("Ahmedabad", CHOOSE({1,2}, B2:B6, A2:A6), 2, FALSE) → Gujarat.

Today, prefer XLOOKUP or INDEX + MATCH: they are easier to read and faster.

Troubleshooting #N/A

Cause How to check Fix
The key really is missing Search the lookup column with Ctrl + F Return a message: IFNA(…,"Not found") or XLOOKUP's 4th argument
Extra spaces =LEN(A2) is longer than expected TRIM both sides, or XLOOKUP(TRIM(B2),TRIM(Prod!A2:A7),…)
Number vs text (101 vs "101") =ISTEXT(B2) differs from the lookup column Convert one side: VALUE() or TEXT(…,"0")
Approximate match on unsorted data Wrong but no error Use exact match (FALSE / 0)
Range not locked Errors appear only in lower rows Add $ or use a Table
Invisible characters =CODE(RIGHT(B2)) returns 160 or 10 SUBSTITUTE and CLEAN
2026 updateLookups in 2026

Case: pricing the Superstore order lines

Suppose the order lines arrive without a price and you must look it up from the product master (products.csv). This is a lookup on 6,323 rows. pandas does the same as XLOOKUP with merge:

Python
import pandas as pd
orders = pd.read_csv("superstore_sales.csv")[["order_id", "product_id", "quantity"]]
products = pd.read_csv("products.csv")
priced = orders.merge(products[["product_id", "product_name", "list_price"]], on="product_id", how="left")   # = XLOOKUP for every row
priced["gross_amount"] = priced.quantity * priced.list_price
print("Rows:", len(priced), "| unmatched product IDs:", priced.list_price.isna().sum())
priced.head(5)
Output
Rows: 6323 | unmatched product IDs: 0
order_id product_id quantity product_name list_price gross_amount
0 IN-2023-00001 P0059 2 Nova Phone 223 18,000 36,000
1 IN-2023-00001 P0050 1 Orbit Appliance 798 3,850 3,850
2 IN-2023-00001 P0011 4 Orbit Table 248 15,520 62,080
3 IN-2023-00002 P0037 2 Crest Storage 946 3,260 6,520
4 IN-2023-00003 P0018 3 Crest Bookcase 170 11,710 35,130
5 rows × 6 columns

Zero unmatched IDs tells you every order line found its product: the same check you should do in Excel with =COUNTIF(price_column,"#N/A") or =SUM(--ISNA(price_column)).

Summary

Key takeaways
Quick quiz6 questions · tap an answer to check it

1. =VLOOKUP("P103",A2:E7,4,FALSE) returns a value from which column of the range?

Answer: C. Column index 4 counts from the first column of the table array: A=1, B=2, C=3, D=4.

2. Why does VLOOKUP return the wrong price (not #N/A) for unsorted data?

Answer: A. Without FALSE, VLOOKUP uses approximate match, which assumes sorted data.

3. Which formula returns the State for the city in H2, where State is column A and City is column B?

Answer: B. MATCH finds the row of the city in column B; INDEX returns column A on that row.

4. Sales of 2,40,000 with tiers at 0, 1,00,000, 2,50,000 and 5,00,000 (approximate match). Which tier is used?

Answer: B. Approximate match returns the largest tier less than or equal to the value.

5. What does XLOOKUP's 4th argument do?

Answer: B. if_not_found replaces #N/A with your own value or message.

6. A lookup returns #N/A even though the ID is visible in both tables. Which is NOT a likely cause?

Answer: C. File format does not affect lookups. Spaces, invisible characters and type mismatches are the usual culprits.
ExerciseGrade lookup

Create a grade table: 0 → F, 40 → D, 55 → C, 70 → B, 85 → A. Write a formula that returns the grade for a mark in B2 using (a) VLOOKUP, (b) XLOOKUP and (c) INDEX + MATCH.

Show solution

With the table in E2:F6 (marks ascending): (a) =VLOOKUP(B2,$E$2:$F$6,2,TRUE) (b) =XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,,-1) (c) =INDEX($F$2:$F$6,MATCH(B2,$E$2:$E$6,1)) (match_type 1 = largest value ≤ B2, needs ascending order).

ExerciseTwo-way lookup

Make a table of 4 regions (rows) × 4 quarters (columns) with sales figures. Add drop-downs for region (H1) and quarter (H2) and return the matching sales value.

Show solution

With regions in A2:A5, quarters in B1:E1 and values in B2:E5: =INDEX($B$2:$E$5,MATCH($H$1,$A$2:$A$5,0),MATCH($H$2,$B$1:$E$1,0)) or =XLOOKUP($H$1,$A$2:$A$5,XLOOKUP($H$2,$B$1:$E$1,$B$2:$E$5)). Make the drop-downs with Data Validation → List, source $A$2:$A$5 and $B$1:$E$1.

Interview cornerQuestions you may be asked

Q: What are the limitations of VLOOKUP and how do you overcome them? It cannot look left, it defaults to approximate match, it breaks when columns are inserted (hard-coded index), and it returns only the first match. Use XLOOKUP or INDEX + MATCH, and FILTER for multiple matches.

Q: Why is INDEX + MATCH considered more flexible than VLOOKUP? The lookup and return ranges are separate, so it can look left, it is not affected by inserted columns, it supports two-way lookups, and it can be faster on large sheets because it only reads the columns it needs.

Q: How would you look up a value using two criteria? XLOOKUP(1,(range1=x)*(range2=y),return_range), INDEX/MATCH with the same multiplied condition, or a helper column that concatenates the two keys.

Practise more online

Free, trusted places to test yourself on this topic. Links open the original site in a new tab.