Work & productivity / QUICK REFERENCE

Excel lookups: XLOOKUP, VLOOKUP and INDEX/MATCH

Find values by key, control matching, combine criteria and handle missing results with practical Excel formulas.

36 entries

Find an exact value

First matching price: =XLOOKUP(F2,A2:A100,D2:D100,"Not found")
Search IDs in A and return the corresponding price from D. [1][2]
Search in a right-hand column: =XLOOKUP(F2,C2:C100,A2:A100,"Not found")
Unlike VLOOKUP, XLOOKUP can return a column to the left. [1][2]
Hold ranges while copying: =XLOOKUP(F2,$A$2:$A$100,$D$2:$D$100,"Not found")
Absolute ranges do not shift when filled down. [1][2]
Return adjacent columns: =XLOOKUP(F2,A2:A100,B2:C100,"Not found")
Dynamic Excel spills two columns into free cells. [1][2]
Trim entered key: =XLOOKUP(TRIM(F2),A2:A100,D2:D100,"Not found")
Removes ordinary extra spaces in the key cell. [1][2]
Convert numeric text key: =XLOOKUP(VALUE(F2),A2:A100,D2:D100,"Not found")
Use only when stored IDs are real numbers. [1][2]
Find a key's position: =XMATCH(F2,A2:A100)
Returns an offset beginning at one within A2:A100. [1][2]
Test whether a key exists: =ISNUMBER(XMATCH(F2,A2:A100))
TRUE when a matching ID was found. [1][2]
Count repeated IDs: =COUNTIF(A2:A100,F2)
Identifies duplicate lookup keys before choosing a single match. [1][2]

Last matching row: =XLOOKUP(F2,A2:A100,D2:D100,"Not found",0,-1)
Reverse search finds the final match in row order. [1][2]
Exact or next smaller: =XLOOKUP(F2,A2:A100,D2:D100,"Not found",-1)
Fallback to the nearest smaller value when exact is missing. [1][2]
Exact or next larger: =XLOOKUP(F2,A2:A100,D2:D100,"Not found",1)
Fallback to the nearest larger value. [1][2]
Match partial text: =XLOOKUP("*"&F2&"*",A2:A100,D2:D100,"Not found",2)
Wildcard mode supports * and ?; prefix special literals with ~. [1][2]
Binary search ascending: =XLOOKUP(F2,A2:A100,D2:D100,"Not found",0,2)
Only when all IDs are sorted ascending. [1][2]
Binary search descending: =XLOOKUP(F2,A2:A100,D2:D100,"Not found",0,-2)
Only when all IDs are sorted descending. [1][2]
Find final position: =XMATCH(F2,A2:A100,0,-1)
Searches for an exact match from bottom to top. [1][2]
Look up approximate position: =XMATCH(F2,A2:A100,-1)
Exact match or next smaller position. [1][2]
Spot missing keys: =ISNA(XMATCH(F2,A2:A100))
TRUE means that XMATCH found no exact key. [1][2]

Classic exact VLOOKUP: =VLOOKUP(F2,A2:D100,4,FALSE)
Matches IDs in the first table column and returns its fourth column. [3][2]
Lock classic table: =VLOOKUP(F2,$A$2:$D$100,4,0)
0 means exact match, and dollar signs lock the range. [3][2]
Approximate VLOOKUP: =VLOOKUP(F2,A2:D100,4,TRUE)
Requires first-column values sorted ascending. [3][2]
Hide only missing keys: =IFNA(VLOOKUP(F2,A2:D100,4,FALSE),"Missing")
Other calculation errors stay visible. [3][2]
INDEX plus MATCH: =INDEX(D2:D100,MATCH(F2,A2:A100,0))
Exact match with return column anywhere. [3][2]
Exact MATCH offset: =MATCH(F2,A2:A100,0)
Returns a one-based offset within the range. [3][2]
Lookup a table intersection: =INDEX(B2:E100,MATCH(G2,A2:A100,0),MATCH(H2,B1:E1,0))
Matches a row key and a column heading. [3][2]
Horizontal lookup: =HLOOKUP(F2,A1:J4,3,FALSE)
Searches the first row and returns the third row of the table. [3][2]
Fixed row/column access: =INDEX(B2:E100,4,2)
Fourth row and second column of the supplied block. [3][2]

Match two fields: =XLOOKUP(1,(A2:A100=G2)*(B2:B100=H2),D2:D100,"Not found")
Boolean arrays must align; TRUE*TRUE becomes one. [1][2]
Return every match: =FILTER(D2:D100,A2:A100=G2,"No matches")
FILTER returns all matching rows, not only the first. [1][2]
Apply two row conditions: =FILTER(A2:D100,(A2:A100=G2)*(B2:B100=H2),"None")
Both criteria must hold for the row to appear. [1][2]
Add matching amounts: =SUMIFS(D2:D100,A2:A100,G2,B2:B100,H2)
Sum qualifying values instead of selecting a single match. [1][2]
Find a named return column: =XLOOKUP(G2,A2:A100,XLOOKUP(H2,B1:E1,B2:E100),"Not found")
The inner XLOOKUP selects a column by header. [1][2]
Show first three matches: =TAKE(FILTER(A2:D100,A2:A100=G2),3)
Returns up to three rows; no matches still yields an error. [1][2]
Return nonadjacent columns: =CHOOSECOLS(FILTER(A2:D100,A2:A100=G2),1,4)
Shows the first and fourth columns of the filtered result. [1][2]
Name a key with LET: =LET(key,G2,XLOOKUP(key,A2:A100,D2:D100,"Not found"))
Local variable makes longer formulas easier to inspect. [1][2]
Catch #N/A only: =IFNA(XLOOKUP(G2,A2:A100,D2:D100),"No record")
Avoid hiding unrelated formula errors with broad IFERROR. [1][2]

Reserved placement, separate from reference controls. No ad network is active.

Updated 2026-10-20Microsoft Excel 365 / 2024

cheatsheetatlas.com/sheets/excel-lookups/ · Updated 2026-10-20 · Microsoft Excel 365 / 2024