Skip to content
Educora
EN
AzərbaycancaEnglishРусскийTürkçe
My learningEducora StudioSettings
Intermediate15 min10 / 27

Looking things up: VLOOKUP and XLOOKUP

Learn to find a product's name and price from its code automatically: how VLOOKUP works, what #N/A means and why the modern XLOOKUP is better.

Check yourself
In this lesson you will learn
  • Explain the four arguments of VLOOKUP and build an exact-match lookup
  • Find the cause of #N/A and other typical errors
  • Know the advantages of XLOOKUP

A stationery shop's price list has hundreds of products. The cashier types only the code — the product name and price should appear on their own. Searching the list by eye every time is slow and error-prone. One of Excel's most famous functions does this job: VLOOKUP (DÜŞEYARA in Turkish Excel).

How VLOOKUP works

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
where:
  • lookup_valuethe value to look for, e.g. a product code
  • table_arraythe table; the search happens in its first column
  • col_index_numwhich column of the table to return (1, 2, 3…)
  • range_lookupFALSE (or 0) — exact match; TRUE — approximate match
  1. 1
    Take the value

    =VLOOKUP(E2,$A$2:$C$7,3,FALSE) first takes the code in E2, for example P104.

  2. 2
    Search the first column

    Excel searches A2:A7 from top to bottom for P104 and stops at the first row where it finds it.

  3. 3
    Count to the right

    In that row it moves to column 3 of the table: A is 1, B is 2, C is 3.

  4. 4
    Return the result

    The price from column C is returned — 18 ₼. If the code isn't found, the result is #N/A.

Interactive
Loading simulation…
A price list (in manat) and a lookup by code.

Approximate match: looking up by bands

A small table instead of nested IFs

E2:F5 holds a scale: column E has 0, 50, 75, 90 (lower limits in ascending order), column F has 2, 3, 4, 5. The score in B2 is 83. How do you find the mark?

Show solution
=VLOOKUP(B2,$E$2:$F$5,2,TRUE)
With TRUE, Excel finds the largest limit that is less than or equal to 83 — that is 75.
Column 2 of that row: 4.
Requirement: the first column must be sorted in ascending order.

XLOOKUP: the modern replacement

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
where:
  • lookup_arraythe column to search in
  • return_arraythe column to take the result from
  • if_not_foundtext to show when nothing is found (optional)

XLOOKUP is available in Microsoft 365, Excel 2021 and newer. The same lookup is written like this: =XLOOKUP(E2,A2:A7,C2:C7,"Code not found"). There is no column number to count, exact match is the default, and the result column can even be to the left of the lookup column. Educora's practice sheet supports only VLOOKUP, but do try XLOOKUP in real Excel.

FeatureVLOOKUPXLOOKUP
Default matchapproximate (you must add FALSE)exact
Can look to the left?noyes
When you insert a columnthe column number goes out of datethe formula stays correct
When nothing is found#N/A (needs IFERROR)your own text (if_not_found)
Versionsall versionsMicrosoft 365, Excel 2021+

Key points

  • VLOOKUP searches the first column of a table and returns a value from the column you specify.
  • For an exact match the 4th argument must be FALSE (or 0); lock the table with $.
  • #N/A means the value was not found; show a clear message with IFERROR.
  • An approximate match with TRUE works with bands, but the first column must be sorted ascending.
  • XLOOKUP matches exactly by default, can look left and doesn't break when columns are inserted.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10
Where in the table does VLOOKUP look for the lookup value?