Free AI Excel formula explainer

In one sentence: It looks up the code in A2 on the Prices sheet and returns the matching value from column C, or "Not found" if there's no match. Piece by piece: 1. MATCH(A2, Prices!$A:$A, 0) — finds the row where column A of the Prices sheet exactly equals A2. The 0 means exact match only. 2. INDEX(Prices!$C:$C, ...) — returns the value from column C on that same row, probably the price. 3. IFERROR(..., "Not found") — if MATCH can't find the code, it shows Not found instead of #N/A. 4. The $ signs keep both column references fixed when you fill the formula down. Likely cause: the codes look the same but aren't identical. Usually one side has an extra space, or one side is stored as a number and the other as text. Test with =A2=Prices!A5 (using a row where you see the code): FALSE confirms it. Fix spaces with =IFERROR(INDEX(Prices!$C:$C, MATCH(TRIM(A2), Prices!$A:$A, 0)), "Not found"), or retype one column so both are text or both are numbers. Watch out for: - IFERROR hides every error, so a typo in a sheet name also shows Not found. Remove IFERROR while testing. - Whole-column references are fine in modern Excel but can slow very large older files; use $A$2:$A$5000 if needed. - If two rows share a code, only the first one's value is returned.

In one sentence: It adds up column D for rows where column B is West and the date in column A falls in March, of any year. Piece by piece: 1. B2:B500="West" — checks each row and gives TRUE or FALSE for whether column B says West. 2. MONTH(A2:A500)=3 — gives TRUE for rows whose date in column A is in March. 3. The * between them — multiplies the TRUE/FALSE results, which turns them into 1s and 0s. A row counts as 1 only if both tests are true. 4. *D2:D500 — multiplies that 1 or 0 by the amount in column D, so only matching rows keep their amount. 5. SUMPRODUCT(...) — adds all of those results together. Watch out for: - It includes March from every year in the data. To limit it to one year, add *(YEAR(A2:A500)=2026). - A blank or text cell in column A makes MONTH return an error or a wrong month, which can break the total. Make sure column A holds real dates. - Text in column D, such as "n/a", causes a #VALUE! error. SUMIFS with date ranges is often easier to read and ignores text amounts.

In one sentence: It averages the values in column D of the Data sheet for rows where column B matches the value in F1, and shows 0 when nothing matches. Piece by piece: 1. Data!B:B=F1 — checks every row of column B on the Data sheet against the value in F1. 2. FILTER(Data!D:D, ...) — keeps only the column D values from rows that passed the check. 3. LET(sales, ...) — gives that filtered list the name "sales" so the formula can use it twice without recalculating it. 4. ROWS(sales)=0 — checks whether the filtered list is empty. 5. IF(..., 0, AVERAGE(sales)) — returns 0 if nothing matched; otherwise the average of the matching values. Watch out for: - FILTER and LET need Excel for Microsoft 365 or Excel 2021 and later. In older Excel this shows #NAME?. - If nothing matches, FILTER actually returns a #CALC! error rather than an empty list, so the ROWS check may not catch it. A safer version is =LET(sales, FILTER(Data!D:D, Data!B:B=F1, ""), IF(COUNT(sales)=0, 0, AVERAGE(sales))). - Showing 0 for "no data" can be mistaken for a real average of zero. Consider returning "" or "No sales" instead.