Business (Non-IT) tutorial

Excel Lookups: VLOOKUP and XLOOKUP

Pull matching values from another table with VLOOKUP and the newer XLOOKUP, and avoid the most common errors.

  • Beginner
  • 2 min read
  • Free

Lookups find a value in one table and return related information, such as a price for a product code or a manager for an employee ID.

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

Example: product codes are in column A of a price list in A2:C100, and prices are in the third column:

=VLOOKUP(E2, $A$2:$C$100, 3, FALSE)
  • FALSE asks for an exact match. Leave it out and Excel may return a wrong “approximate” match.
  • VLOOKUP only looks to the right of the first column.
  • Use $ to lock the table range before copying the formula down.

XLOOKUP

Available in Microsoft 365 and Excel 2021 or later, XLOOKUP is simpler and more flexible:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
=XLOOKUP(E2, A2:A100, C2:C100, "Not found")
  • It can look left or right.
  • It does exact matches by default.
  • It returns your own message instead of #N/A when nothing matches.

Fixing #N/A errors

  • Extra spaces: clean the data with TRIM().
  • Numbers stored as text: convert them so the types match.
  • The value really is missing: wrap the formula in IFNA() or use XLOOKUP’s if_not_found argument.

Want a live class on this?

An advisor will call you within one business day to recommend a course and a batch that fits your schedule.

  1. An advisor calls you within one business day.
  2. You talk through your background and goals for about 20 minutes.
  3. You get a recommended program and batch, with no obligation to enroll.
info@stratedgeitconsulting.com +1 (302) 434-8889 ext. 101 Monday to Saturday, 9 AM to 7 PM ET

Tell us about you

Takes about a minute. Fields marked optional can be skipped.

Enter your full name.
Enter an email address like name@example.com.
Enter a phone number so an advisor can call you.
Choose what you’re interested in.
Preferred batch
Tick the box so an advisor can contact you.

We use these details as described in our Privacy Policy. By submitting, you agree to our Terms and Conditions.