Lookup practice round

1 / 4

Two lookups for each order: the price from the price list, and the customer's name from the customer list.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABCDEFGHIJKL
1OrderProductCustomer IDPriceCustomerProductPriceNameCustomer ID
2ORD-2380WhiteboardCU322Monitor Stand27.5Sam NovakCU487
3ORD-9589USB-C HubCU292Headset32.99Isla WalshCU292
4ORD-3456Desk OrganiserCU409Office Chair204.99Victor TanakaCU295
5ORD-1112HeadsetCU409Bookshelf82.49Mateo NovakCU409
6ORD-5454Office ChairCU292USB-C Hub34.5Hiro TanakaCU322
7ORD-3951Monitor StandCU487Notebook A55.99Wen RossiCU433
8Filing Cabinet118.25
9Desk Organiser17.25
10
11
12
13
price list
customers
answers
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How VLOOKUP and XLOOKUP and INDEX and MATCH works

  • What is this round for?

    Real sheets mix several lookups at once. This round puts together what the lookup lessons covered: two lookup tables, a value that isn't in one of them, and a return column to the left of the search column.

  • Which lookup should I use?

    • XLOOKUP does everything here: it can look left and has a not-found part. Use it if your Excel has it (365 and 2021 or newer).
    • VLOOKUP works for the price, wrapped in IFNA for the missing product. It can't return the name, because the name is to the left of the ID.
    • INDEX and MATCH work everywhere, in any direction.

  • What should I check before copying down?

    • Every lookup range is locked with $.
    • Each lookup searches the right column and returns the right one.
    • A missing value shows "Not found", not #N/A.

  • What next?

    Try the Lookup & Merge tool on your own files: it writes these formulas for your data and explains every value it couldn't match.

Know someone fighting with a spreadsheet? Send them this page.