Formula Helper

Free Excel Formula Generator

Build complex spreadsheet formulas without memorizing syntax. Search our catalog or use the interactive builder to generate copyable formulas.

XLOOKUP

Lookup & Reference

VLOOKUP

Lookup & Reference

INDEX MATCH

Lookup & Reference

SUMIF

Math & Trigonometry

COUNTIF

Statistical

Lookup & Reference

XLOOKUP Formula

The modern successor to VLOOKUP. Finds a value in a range or array and returns the corresponding value from another range or array. Safer, simpler, and searches in any direction.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Parameters

lookup_valueREQUIRED

The value you want to search for (e.g. a product ID or name).

lookup_arrayREQUIRED

The column or row range containing the search keys (e.g. A2:A50).

return_arrayREQUIRED

The column or row range containing the values to return (e.g. C2:C50).

if_not_foundOPTIONAL

Text or value to return if no match is found (e.g. "Not Found"). Defaults to error if empty.

match_modeOPTIONAL

Specify match type: 0 for exact match (default), -1 for exact/next smallest, 1 for exact/next largest, 2 for wildcard.

Example Usage

=XLOOKUP("Widget A", A2:A10, B2:B10, "Out of Stock")

Searches for "Widget A" in column A (A2:A10) and returns the matching price from column B (B2:B10). If "Widget A" isn't found, it returns "Out of Stock".

Common Mistake / Pitfall

XLOOKUP is only available in newer versions of Excel (Excel 365, Excel 2021+). If you share spreadsheets with users running older Excel versions, they will see a #NAME? error.

Master Spreadsheet Formulas Instantly

Modern business depends on spreadsheet calculations, but formatting parameters correctly in formulas like VLOOKUP, INDEX MATCH, or SUMIF is notoriously difficult. A single misplaced comma or mismatched range can trigger frustrating errors like #N/A, #VALUE!, or #REF!. The VisualizeMyData Excel Formula Generator removes the guesswork by showing exactly how parameters relate to your goals.

Formulas You Can Generate:

  • XLOOKUP: The modern way to search rows. Can look left, default fallback values, and works without column index numbers.
  • VLOOKUP: Standard vertical lookup. Searches the leftmost column of your table and returns values to the right.
  • INDEX MATCH: The industry standard for large spreadsheets, supporting leftward matching and dynamic columns.
  • SUMIF: Sums range cells depending on whether they satisfy a target logical condition.
  • COUNTIF: Scans cell columns and counts cells meeting criteria (e.g. tracking absences or passing scores).

Perfect for Excel & Google Sheets

All generated formulas utilize standard syntax supported by both Microsoft Excel and Google Sheets. Simply click the copy button, double-click a cell in your spreadsheet, paste the formula, and press Enter.