PathTailor
FRSign inBuild my path
PathTailor
Build my pathSign inLire en français

RECHERCHEX (XLOOKUP) and INDEX/EQUIV (INDEX/MATCH)

First lesson of the Excel: Lookup and Logical Functions course, free to read: about 20 minutes, 4 exercises and 3 quiz questions. This is the catalogue's base lesson; in the student space, it is rewritten for your profile.

Objectives

  • Match two tables on a key with RECHERCHEX (XLOOKUP)
  • Use INDEX and EQUIV (MATCH) to look up in any direction
  • Do an approximate lookup in a threshold grid

RECHERCHEXINDEX and EQUIVRECHERCHEVApproximate lookup

The need: matching two tables

In everyday work, information is rarely in a single table. Orders give a product reference and a quantity; the price is in the catalog. Rather than copying prices by hand, you ask Excel to look up in the catalog the row that carries the same reference. That is the job of lookup functions.

The column that links the two tables is called the key (here Ref). It must be unique in the reference table: if P003 appears twice in the catalog, Excel will return the first occurrence without warning you.

RECHERCHEX

RECHERCHEX (XLOOKUP) is available in Microsoft 365, Excel 2021 and later versions, as well as in Excel for the web. Its syntax:

=RECHERCHEX(valeur_cherchée;plage_de_recherche;plage_de_résultat;[si_non_trouvé];[mode_correspondance])

For the price of the order on row 2:

=RECHERCHEX(B2;Produits!$A$2:$A$9;Produits!$D$2:$D$9)

Excel looks for the value of B2 in the references column and returns the value located on the same row in the prices column. Both ranges must have the same height. They are locked as absolute so that the formula can be copied down without the catalog “sliding”.

The optional fourth argument says what to display if the key is missing (by default, the #N/A error). The fifth sets the match mode: 0 exact (the default), -1 exact or the next smaller value, 1 exact or the next larger value, 2 wildcard characters.

RECHERCHEV, the ancestor

You will often come across RECHERCHEV (VLOOKUP) in existing workbooks:

=RECHERCHEV(B2;Produits!$A$2:$E$9;4;FAUX)

It looks for B2 in the first column of the table and returns the 4th column. Two traps: forgetting FAUX (FALSE) as the last argument (the lookup becomes approximate and can return a wrong price with no visible error), and inserting a column in the table, which shifts the number 4. RECHERCHEX has neither of these flaws.

INDEX and EQUIV

The INDEX/EQUIV (INDEX/MATCH) pair works in all versions of Excel. EQUIV returns the position of a value in a range; INDEX returns the value located at a given position:

=EQUIV("P007";Produits!$A$2:$A$9;0)                       -> 7
=INDEX(Produits!$B$2:$B$9;7)                               -> Caisson 3 tiroirs
=INDEX(Produits!$B$2:$B$9;EQUIV("P007";Produits!$A$2:$A$9;0))

The 0 in EQUIV enforces an exact match: never forget it. Since the two ranges are independent, the result column can be to the left of the key, which RECHERCHEV cannot do. INDEX also accepts a row and a column: with two EQUIV, you get a two-way lookup (a reference and a column name).

Approximate lookup: threshold grids

Some tables do not contain every possible value but thresholds: a commission grid (0 €: 0 %, 2 000 €: 3 %, 4 000 €: 5 %, 6 000 €: 7 %). For revenue of 4 400 €, you want the rate of the 4 000 threshold, that is the equal or next smaller value:

=RECHERCHEX(B2;Grille!$A$2:$A$5;Grille!$B$2:$B$5;;-1)

The fourth argument is left empty (two consecutive semicolons) and mode -1 does the rest. A revenue exactly equal to a threshold (4 000) does get the rate of that threshold.

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

  1. TaskDifficulty 12 hints

    Value orders with RECHERCHEX

    Create the file C:\Formation\produits.csv with Notepad, paste in the content below, save it, then open it in Excel by double-clicking it. The data occupies the range A1:E9. Rename the sheet to Produits. If everything lands in column A, use Data > Text to Columns with the semicolon as the delimiter.

    Ref;Designation;Famille;PrixHT;Stock
P001;Ecran 24 pouces;Informatique;149;12
P002;Clavier sans fil;Informatique;39;40
P003;Fauteuil;Mobilier;229;5
P004;Bureau droit;Mobilier;310;3
P005;Lampe LED;Eclairage;45;18
P006;Casque audio;Informatique;79;0
P007;Caisson 3 tiroirs;Mobilier;139;7
P008;Multiprise;Electricite;19;55

    In the same workbook, add a sheet named Commandes and enter (or paste then convert) the following data in the range A1:C5:

    NumCde;Ref;Quantite
C101;P003;2
C102;P008;10
C103;P001;4
C104;P006;1

    In the Commandes sheet:

    1. In D1, enter PrixHT; in D2, write a RECHERCHEX that returns the price of the reference in B2 from the Produits sheet. Copy it down to D5.
    2. In E1, enter Montant; in E2, calculate quantity x price, then copy it down.
    3. In E6, calculate the total of the amounts.
    • The Produits sheet ranges are absolute references
    • D2 displays 229 and D5 displays 79
    • E4 displays 596
    • E6 displays 1323

  2. TaskDifficulty 21 hint

    INDEX/EQUIV in both directions

    On the previous workbook, in a new sheet Recherches:

    1. In A1, enter P007. In B1, display the designation of this reference with INDEX and EQUIV.
    2. In A2, enter Lampe LED. In B2, display its reference (the column located to the left of the designation).
    3. In A3, enter P004 and in A4 Stock. In B3, write a two-way INDEX formula that returns the value of the column named in A4 for the reference in A3.
    4. Replace A4 with PrixHT and observe B3.
    • Every formula uses EQUIV with 0 as the last argument
    • B1 displays Caisson 3 tiroirs
    • B2 displays P005
    • B3 displays 3, then 310 when A4 is PrixHT

  3. TaskDifficulty 22 hints

    Commission grid

    Create the file C:\Formation\grille.csv with Notepad, paste in the content below, save it, then open it in Excel by double-clicking it. The data occupies the range A1:B5. Rename the sheet to Grille. If everything lands in column A, use Data > Text to Columns with the semicolon as the delimiter.

    Seuil;Taux
0;0%
2000;3%
4000;5%
6000;7%

    In the same workbook, add a sheet named Commissions and enter (or paste then convert) the following data in the range A1:B4:

    Commercial;CA
Martin;4400
Bernard;3700
Petit;4000

    In the Commissions sheet:

    1. In C1, enter Taux; in C2, write a RECHERCHEX in mode -1 that returns the grid rate matching the revenue (CA). Copy it down to C4 and format as a percentage.
    2. In D1, enter Commission; in D2, calculate CA x rate and copy it down.
    3. In D5, total the commissions.
    • C2 (Martin, 4400) displays 5 %, C3 (Bernard, 3700) displays 3 %
    • C4 (Petit, 4000, exactly a threshold) displays 5 %
    • D2 = 220, D3 = 111, D4 = 200
    • D5 displays 531

  4. Multiple choiceDifficulty 21 hint

    RECHERCHEV without FAUX

    What is the risk of writing =RECHERCHEV(B2;Produits!$A$2:$E$9;4) without a fourth argument?

    • None, the lookup is exact by default
    • An approximate lookup that can return a wrong price without displaying an error
    • A #NOM? error
    • The formula looks in the 4th row

End-of-lesson quiz

  1. Which RECHERCHEX argument lets you display text when the key is missing?

    • The third
    • The fourth (si_non_trouvé)
    • The sixth

  2. What does EQUIV(“P003”;A2:A9;0) return if P003 is in A4?

    • 4
    • 3
    • P003

  3. For a threshold grid, which RECHERCHEX match mode should you use?

    • 0
    • -1
    • 2

The rest of the course

  1. Nested SI (IF), SI.CONDITIONS (IFS), ET (AND) and OU (OR)20 min
  2. Conditional Sums and Counts: SOMME.SI.ENS (SUMIFS), NB.SI.ENS (COUNTIFS)20 min
  3. Text and Date Functions20 min
  4. Handling Errors: SIERREUR (IFERROR) and SI.NON.DISP (IFNA)15 min
Build my pathSee the full syllabus

Excel: Lookup and Logical Functions · Intermediate · 1 h 35 min