Learn Excel
EN
Sets how numbers, dates and formulas look, so everything works just like your own Excel.

Home › Learn Excel › Lesson 8

VLOOKUP and XLOOKUP in Excel

Find a value in a table: VLOOKUP for columns, HLOOKUP for rows and XLOOKUP for everything.

  1. Look up a value: VLOOKUP. VLOOKUP searches the first column of a table and returns a value from the same row.
    You can type the value you look for straight into the formula, in quotes. Find the price of a Latte in F2
  2. Use a cell as the lookup value. Instead of typing the value, point to a cell. Change the cell and the result follows, without touching the formula. Find the calories of the item in E2
  3. Approximate match: price bands. With TRUE as the last argument, VLOOKUP doesn't need an exact match. It takes the last row that is not larger than your value. The first column must be sorted from small to large. Find the shipping cost for the 7 kg parcel in E2
  4. Exact or approximate?.
  5. Across a row: HLOOKUP. HLOOKUP is VLOOKUP turned on its side: it looks along the top row and returns a value from a row below. Look up the sales for the month in A6, in B6
  6. The modern way: XLOOKUP. XLOOKUP looks for a value in one column and returns the value from the same row in another column. Find the price of product P-104 in F2
  7. Your turn: find a department. No tips this time. Write the whole formula yourself; press Hint if you get stuck. Show the department of employee 1004 in F2, using E2

← All lessons