VLOOKUP, INDEX/MATCH, XLOOKUP: get the right one with Crade

Lookups are the most-used spreadsheet feature for analytical work, and the one most likely to silently return wrong answers. The functions (VLOOKUP, HLOOKUP, INDEX/MATCH, XLOOKUP) each have specific column ordering rules, error behaviour, and tool support. Crade reads your sheet and gives you the right one for your situation.

What you put on your screen

• Two tables visible: the lookup source (Rates, Prices, Master list) and the target (where you want the formula)
• Headers visible in both
• The cell where the formula goes selected

Ideally both tables are on the same tab or visible across tabs (you may need to scroll). Crade reads the column letters and structure to write the right formula.

What you say to Crade

Or describe in plain English: "I want the price for each item, the prices are in another sheet". Crade reads both sheets and gives you the formula.

Which function Crade picks

• XLOOKUP: if your spreadsheet supports it (modern Excel, Google Sheets). Simplest syntax, no column ordering tricks.
• INDEX/MATCH: if XLOOKUP is not available or if you want flexibility (lookups to the left, multi-column, custom error handling).
• VLOOKUP: if your sheet is older or your colleagues are not familiar with INDEX/MATCH. Works if your lookup column is the leftmost in the source.
• HLOOKUP: rare, for horizontal lookups across rows instead of down columns.

Step-by-step

1. 1

   Open your sheet with both tables visible

   Same tab or adjacent tabs, scroll so both are readable.

2. 2

   Click the target cell

   The cell where the formula should go.

3. 3

   Ask Crade for the formula

   Describe the lookup in plain English. Mention which column has the key, which has the value.

4. 4

   Paste the formula

   Crade gives you the exact formula with your column letters. Paste into the cell, hit Enter.

5. 5

   Check for #N/A or #REF errors

   If you get errors, paste back to Crade: "I got #N/A on row 5, what is wrong?". Usually a typo in the key, a leading/trailing space, or a wrong sheet reference.

6. 6

   Apply to the whole column

   Drag the fill handle down. The relative references update for each row.

Common issues Crade can debug

• Trailing whitespace: "Customer Co " with a space does not match "Customer Co". Crade flags it and suggests TRIM().
• Case mismatch: VLOOKUP is case-insensitive by default; INDEX/MATCH can be made case-sensitive with EXACT(). Crade tells you which fits.
• Number vs text: a column that looks like numbers might be stored as text. Crade spots and gives you the fix.
• Wrong column count: VLOOKUP needs the column index relative to the source range. Crade writes the index correctly.
• Approximate vs exact match: VLOOKUP defaults to approximate (which silently returns wrong values). Crade uses FALSE for exact match always unless you want range lookups.

Tips for accuracy

• Use XLOOKUP if you have it. It is the easiest and most forgiving.
• For VLOOKUP, always pass FALSE as the fourth argument unless you specifically want range lookup.
• For INDEX/MATCH, the MATCH function returns the row position. Use 0 as the match type for exact match.
• When the lookup fails, paste the error to Crade with the row number. Crade walks you through diagnosis.
• For repeated lookups against the same source, consider naming the source range. Easier to read and Crade writes the formula with the name.

The whole loop in one sentence

Two tables on screen, plain description of the lookup, exact formula back. Lookups stop being a source of silent wrong answers.