Get XLOOKUP & SUMIFS Formulas from Free AI Chatbots

If you know what you want a spreadsheet to do but can’t remember which function does it, you don’t have to search through function lists. You can describe your Excel or Google Sheets data in plain English to a free AI chatbot such as ChatGPT, Claude, or Gemini and ask it to write the formula for you. This guide shows how to describe your data so the answer fits your sheet, walks through two common formulas (XLOOKUP for looking up a value and SUMIFS for adding up numbers that match conditions), and explains how to check the result before you rely on it.

What You Need (All Free)

  1. A spreadsheet app. The examples in this guide use Google Sheets, which you can use with a Google account. The same formulas work in Excel 2021 and later and in Excel for Microsoft 365. XLOOKUP is not available in Excel 2016 or Excel 2019.
  2. A free AI chat tool. This guide uses only the free plans of ChatGPT, Claude, and Gemini. You only need the regular chat window. No add-ons or paid features are involved.

You will copy the formula from the chat and paste it into a cell yourself, so the AI never needs access to your file.

Describe Your Sheet So the AI Gets It Right

The AI can’t see your spreadsheet. It only knows what you tell it. A vague question such as “how do I look up a price?” gets a generic answer that you then have to adapt. A specific description gets a formula you can paste straight in. Include these four things:

  1. Sheet names. If the formula pulls data from another tab, give the exact tab name, such as Products or Orders.
  2. Column letters and headers. Say which column holds what, for example “Product ID is in column A, Price is in column C.”
  3. The rows that hold data. Give the range, such as rows 2 to 200, so the AI doesn’t guess.
  4. The result you want and where it goes. Say what the formula should return and which cell it goes in, including what to show when nothing matches.

Here is an example prompt that covers all four:

I use Google Sheets. Sheet 'Products' has Product ID in A2:A200 and Price in C2:C200. In sheet 'Orders', the Product ID is in A2. Write a formula for B2 that returns the price, and shows 'Not found' if there is no match.

It also helps to say which app you use, because Excel and Google Sheets sometimes behave differently (for other functions). Also ask the AI to explain each part of the formula. That way you learn what it does and can spot mistakes.

A note on privacy: don’t paste real customer names, email addresses, or other private data into the chat. The AI only needs the structure of your sheet, meaning the column letters, headers, and ranges. If you want to show sample rows, make up the values.

Example 1: Look Up a Price with XLOOKUP

For the prompt above, an answer might look like this:

=XLOOKUP(A2, Products!A2:A200, Products!C2:C200, "Not found")

Here is what each part means:

  1. A2 is the value to look for: the Product ID on the current row of the Orders sheet.
  2. Products!A2:A200 is where to look for it: the Product ID column on the Products sheet.
  3. Products!C2:C200 is where the answer comes from: the Price column. When a match is found in the 15th cell of the first range, the formula returns the value from the 15th cell of this range.
  4. "Not found" is what to show when there is no match. Without it, the cell shows #N/A.

You will usually want the formula on every row, not just row 2. If you copy it down as it is, the ranges shift by one row each time (A3:A201, A4:A202, and so on), and rows near the top of the price list drop out. To stop that, lock the ranges with $ signs:

=XLOOKUP(A2, Products!$A$2:$A$200, Products!$C$2:$C$200, "Not found")

Now only A2 changes as you fill the formula down (to A3, A4, and so on), while the lookup ranges stay fixed. If the AI gives you a version without $, ask it: “I’m going to copy this formula down the column. Please lock the ranges.”

Example 2: Add Up Sales with SUMIFS

Say you have a sales sheet where column B is the region, column C is the product, and column D is the sales amount, with data in rows 2 to 100. You want the total sales of Paper in the East region. Describe it like this:

I use Google Sheets. In sheet 'Sales', column B has Region, column C has Product, and column D has Amount, in rows 2 to 100. Write a formula that adds up Amount where Region is "East" and Product is "Paper".

The AI should give you something like:

=SUMIFS(D2:D100, B2:B100, "East", C2:C100, "Paper")

Read it in pairs:

  1. D2:D100 is the range to add up: the sales amounts.
  2. B2:B100, "East" is the first condition: the region must be East.
  3. C2:C100, "Paper" is the second condition: the product must be Paper.

Only rows that meet every condition are added. You can also use comparison operators. Put the operator and the number together inside quotation marks. This version adds up East-region sales over 100:

=SUMIFS(D2:D100, B2:B100, "East", D2:D100, ">100")

Watch the argument order. In SUMIFS, the range to add up comes first. In the older SUMIF function, it comes last. Microsoft’s documentation notes that this difference in argument order is a common source of problems, so check it in any formula you get. If the total looks wrong, check that the first range in your SUMIFS is the column of numbers you want to add.

Check the Formula Before You Trust It

AI answers can be wrong, even when the formula looks reasonable and returns no error. Before you rely on a formula, run through these checks:

  1. Compare the argument order with the official documentation. The syntax line on the Microsoft Support or Google Docs Editors Help page shows the correct order (links are at the end of this guide). Match each part of the AI’s formula to the argument it should be.
  2. Test it on rows where you already know the answer. Pick two or three rows where you already know the answer, work out the result by hand, and compare it with the formula’s result. For XLOOKUP, find the Product ID in the price list yourself and compare the price.
  3. If you get #N/A from XLOOKUP, no match was found. Add the fourth argument (such as "Not found") so missing values are easy to spot.
  4. Make sure the lookup range and the result range are the same size. Google’s documentation says the result range should be the same size as the lookup range. For example, A2:A200 with C2:C200 is fine, but A2:A200 with C2:C150 is not.
  5. Paste the error back into the chat. If the formula shows an error or a number you didn’t expect, copy the exact error message into the same conversation and describe what you see, such as “I get #N/A in B5, but product P-104 is in the Products sheet.” The AI can then suggest a fix, which you check again with steps 1 to 4.

Excel vs. Google Sheets: What’s Different

The formulas in this guide work the same way in both apps, but the help pages use different names for XLOOKUP’s arguments:

Meaning Excel Google Sheets
Value to look for lookup_value search_key
Where to look lookup_array lookup_range
Where the answer comes from return_array result_range
What to show if no match if_not_found missing_value

The order of the arguments is the same in both apps, and in both, XLOOKUP returns #N/A by default when it finds no match. So if the AI explains a formula using Excel’s argument names and you use Google Sheets (or the other way around), the formula itself is still the same.

SUMIFS has the same argument order in both apps, with the range to add up first.

References

Function syntax was checked against the official documentation below. Plan details change often; check the pricing pages for current limits.