Understand and fix a formula someone else wrote

Use an assistant to explain a formula you inherited and find out why it gives the wrong answer.

[[chat Manager: The supplier total looks low this month. Can you check the tracker? | You: The new suppliers all show #N/A. The rate table runs to row 180 now. | Manager: The person who built it left last year. Can you sort it by Friday?]]

---

Paste the formula, not the data

``` =XLOOKUP(A2,$F$2:$F$150,$G$2:$G$150) ```

All the assistant sees is A2 and F2:F150. Tell it A holds the supplier code, and F and G hold the rate table.

Say Excel or Sheets too. XLOOKUP isn't in Excel 2016 or 2019.

---

?? Supplier 1088 goes into row 151 of the rate table, rate and all. What does =XLOOKUP(A2,$F$2:$F$150,$G$2:$G$150) show when A2 is 1088? Its new rate, since the table now includes it + #N/A, because the lookup stops at row 150 0, until the sheet recalculates => The range doesn't grow when the table does. XLOOKUP looks for an exact match by default and shows #N/A when it finds none.

---

[[chat You: I inherited this in Excel: =XLOOKUP(A2,$F$2:$F$150,$G$2:$G$150). A is the supplier code, F:G the rate table. Explain it plainly, then in one line. | Assistant: It finds A2 in F2:F150 and returns the rate from G. In one line: this row's supplier rate, from table rows 2 to 150.]]

---

Ask for **causes**, not a new formula

[[rewrite Fix this formula. | New codes show #N/A, old ones work. I expected a rate for every code. List likely causes and how to check each. | What broke, what you expected]]

"Fix this" gets a formula, not the reason.

---

Three causes to check first

The range stops above the new rows. Numbers stored as text: 1042 won't match "1042". Dollar signs on the wrong part, so copied formulas look up the wrong cell.

---

?! Quick fire: which cause is it? Code 1042 is in table row 40, but shows #N/A. | It's stored as text in one place | The range stops too early | The dollar signs are wrong => They look identical on screen but don't match. A number sitting on the left of its cell is usually text. Every row shows the same rate, whatever the supplier. | The lookup value is locked as $A$2 | Codes are stored as text | The range misses new rows => With $A$2, every copied row looks up the code in row 2. Copied down, the table range slides: F3:F151, then F4:F152. | The table needs dollar signs | The lookup value needs dollar signs => Without $F$2:$F$150, the table moves down a row each time, and the top suppliers fall out of it. A SUMIF covers every row, but the total is short, with no error. | Some amounts are stored as text | The range stops too early => SUMIF skips text cells without a warning, so those amounts never get added. You added rows 151 to 160, and =SUM(H2:H150) didn't move. | The range stops too early | Amounts are stored as text => The new rows sit below row 150, so the sum never sees them.

---

Change **one thing**, then check again

[[flow Extend the range to row 180 | 2 codes still show #N/A, both imported as text | Convert those 2 to numbers | Every code shows a rate]]

Change both at once and it works, but the next time it breaks you'll be guessing.

---

The summary goes on the cell

``` =XLOOKUP(A2,$F$2:$F$180,$G$2:$G$180) ```

The fixed formula, with its summary updated: "Returns this row's supplier rate from table rows 2 to 180."

Add it as a comment. Next time it breaks, whoever opens the sheet starts from that line.

---

?# Your message to the assistant I inherited this formula in {Excel or Google Sheets}: {paste the formula}. {The first column it uses} holds {what that column holds}, and {the range it searches} holds {what that range holds}. Explain each step plainly, then sum it up in one sentence for a cell comment.

Pick a formula in a sheet you use but didn't build. Send the message above to your assistant, then paste its one-sentence summary as a comment on that cell.

Course

Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).