Excel Formula Generator: XLOOKUP, INDEX/MATCH, IFS

Enter your ranges to get 5 ready-made formulas (XLOOKUP, IFS, SUMIFS…) in English or French Excel syntax.

Building XLOOKUP, IFS and SUMIFS formulas without syntax errors

The tool assembles five common Excel formulas from the ranges you enter: XLOOKUP, INDEX/MATCH, IFS, SUMIFS and TEXTSPLIT. It writes them in English syntax (English names, commas) or French syntax (RECHERCHEX, SI.CONDITIONS, semicolons) and explains what each argument does.

The five templates

  • XLOOKUP: lookup value, lookup column, return column and the text shown when nothing is found; the data need not be sorted.
  • INDEX + MATCH: crosses a row and a column of a table, each MATCH set to exact match (argument 0).
  • IFS: three thresholds tested in order with ≥, then TRUE for the default value.
  • SUMIFS: sums a column under two criteria, for instance a city and a minimum date.
  • TEXTSPLIT: splits a cell into columns on a delimiter.

Example: a grade from a score

With the default thresholds the tool produces =IFS(B2>=90, "Excellent", B2>=70, "Good", B2>=50, "Average", TRUE, "Insufficient"). For a score of 82 the first true condition is B2>=70, so the cell shows Good. Because conditions are read from left to right, enter thresholds from highest to lowest: in reverse order, any score of 50 or more would get Average. In French syntax the same formula starts with =SI.CONDITIONS(, uses semicolons and VRAI instead of TRUE.

Limits

  • The tool produces text: it checks neither that the ranges exist nor the result. Test the formula on a few rows.
  • IFS does not exist in Excel 2016 and earlier, XLOOKUP not before Excel 2021 and TEXTSPLIT not before Excel 2024 or Microsoft 365; older versions show #NAME?.
  • Only French and English syntax are offered; a German or Spanish Excel expects its own function names.
  • The example sentence under the formula describes the default settings and does not follow your thresholds.

To copy the formula down without shifting the ranges, lock them with the F4 key ($A$1:$A$100).

Frequently Asked Questions

What is the difference between XLOOKUP and VLOOKUP?

VLOOKUP needs the lookup value in the first column and a return column number, and it uses approximate match when the last argument is omitted. XLOOKUP takes two separate ranges, so it can look to the left, uses exact match by default and accepts a fallback text when nothing is found.

Why does my Excel formula show #NAME?

Excel does not recognise the function name: either your version is too old (XLOOKUP needs Excel 2021 or Microsoft 365) or the language does not match, for example RECHERCHEX typed into an English Excel. Generate the formula in your Excel's syntax.

Should arguments be separated by commas or semicolons?

It depends on regional settings: an Excel set to a locale that uses the comma as decimal separator, such as French, expects semicolons; an English Excel expects commas. The tool switches both the separator and the function names when you toggle between EN and FR.

How do I add a default value to IFS?

End with the condition TRUE followed by the value you want; it applies when no threshold is met. With =IFS(B2>=90, "Excellent", B2>=70, "Good", B2>=50, "Average", TRUE, "Insufficient"), a score of 42 returns Insufficient instead of the #N/A error.

Is this tool really free?

Yes, all tools on Kristools are 100% free and accessible without registration.

Is my data protected?

Yes. All processing is performed directly and securely inside your browser. No personal data is sent to our servers.