158 useful answers · Page 4 of 7
Use formulas, clean imported data, combine tables, and understand calculation errors.
Search this topicWrap the calculation, for example =IFERROR(A2/B2,"Check inputs"). This catches any error from that expression, not just division by zero. Use a message that prompts investigation instead of silently replacing problems with a plausible number. First test the underlying formula so IFERROR does not conceal a wrong reference or broken calculation.
Use =IFNA(VLOOKUP(E2,A2:B100,2,FALSE),"Not found"). It replaces a #N/A result while leaving other error types visible. This is more specific than hiding every error with IFERROR. Check that lookup keys have compatible types and spacing; a missing match can indicate dirty data rather than an absent record.
Use =AND(B2>=70,C2="Complete") to return TRUE only when both conditions hold. Wrap it in IF if you need a label instead of TRUE or FALSE. Keep the criteria explicit and test a row where only one condition passes. AND does not mean either condition is enough; that would require OR.
Use =OR(B2="Urgent",C2>100) to return TRUE when either condition, or both, is true. Test examples where neither condition holds so you understand the boundary. If both conditions must pass, use AND instead. The formula evaluates the conditions you provide; it does not infer whether a row is important for other reasons.
Use =NOT(A2) when A2 contains a logical value. TRUE becomes FALSE and FALSE becomes TRUE. This is useful for reversing a check such as whether a task is complete. Make sure the input is a real boolean or appropriate expression; arbitrary text labels are not automatically interpreted as the opposite status.
Use =SWITCH(A2,"N","New","P","Processing","D","Done","Unknown"). Each code is paired with its label, and the final value handles anything unmatched. This is useful for a short fixed list. For a long or frequently changing mapping, a separate lookup table is easier to audit and maintain than a large formula.
Use =XLOOKUP(E2,C2:C100,A2:A100,"Not found",0). It searches for E2 in column C and returns the aligned value from column A, using exact matching. Lookup and result ranges must correspond row for row. Check duplicate keys because a basic lookup returns a matching record, not necessarily every record with that key.
Use =VLOOKUP(E2,A2:C100,3,FALSE). It searches the first column of A2:C100 for E2 and returns the third column's value from the matching row. FALSE requests an exact match. Omitting that argument can change the behavior, so include it deliberately and check key formatting before trusting a returned result.
Use =HLOOKUP(F1,A1:D4,3,FALSE). It searches the first row of the range for F1 and returns the value from the third row of that range in the same column. FALSE requests an exact match. The row number is relative to the selected range, not necessarily the worksheet's row number.
Use =INDEX(A2:D10,3,2) to return the third row and second column within that range, which is cell B4. The position is relative to the range's upper-left corner. Check those offsets carefully when headers are excluded. Combine INDEX with MATCH when the position should be found from a key rather than hard-coded.
Use =MATCH(E2,A2:A100,0) to find the relative position of an exact match. The first cell in the selected range has position one, even if it is worksheet row two. MATCH returns the position, not the cell's contents or an associated field. Use INDEX with that position to retrieve a corresponding value.
Use =CHOOSE(A2,"Small","Medium","Large") when A2 contains 1, 2, or 3. The number selects a position in the list; it is not a text lookup. Validate the input because zero, negative values, or an index beyond the available choices causes an error. Keep longer mappings in a table for easier maintenance.
If A2 contains B5, use =INDIRECT(A2) to return the value from B5. The text must describe a valid reference. For sheet names with spaces, include the appropriate single quotes in the reference string. Use this only when text-built references are necessary, since they can make a workbook harder to follow and maintain.
Use =ADDRESS(5,3,4) to return C5 as text. The first argument is the row, the second is the column, and the third selects relative address formatting here. This gives an address string rather than the contents of C5. Use a real reference or INDIRECT only if you also need the referenced value.
Use =ROW() to return the worksheet row containing the formula, or =ROW(A7) to return 7. This is a position, not a permanent record ID. Inserting, deleting, or moving rows can change position-based numbering. Use fixed identifiers when records must keep the same identity after sorting or rearrangement.
Use =COLUMN(D1) to return 4, since A is column one. With no argument, COLUMN returns the position of the formula's own column. This can help build position-based formulas, but it does not identify a field by its header name. Recheck formulas that depend on positions after restructuring a sheet.
Use =ROWS(A2:D20) to return 19. It measures the range's height, including rows that contain no data. This differs from counting records or nonblank values. Use a field-based count when you need the number of populated entries, and make sure headers or extra worksheet rows are not mistaken for actual records.
Use =COLUMNS(B2:F10) to return 5. The function measures the range's width, not the number of filled cells or headings. This is useful when checking array dimensions before combining ranges. A blank column still counts, so it cannot by itself tell you how many fields contain useful information.
Use =SUM(OFFSET(A1,2,1,3,1)) to sum a three-row, one-column range starting two rows down and one column right of A1: B3:B5. Keep the dimensions and offsets explicit. Avoid pointing outside the sheet or back into the formula's own output, which can create reference or circular-calculation errors.
Use =DATE(A2,B2,C2) when the cells contain numeric year, month, and day values. Format the result as a date. Check the inputs because DATE can normalize out-of-range values, such as rolling an extra month into the following year. A returned date is therefore not proof that every original component was valid.
Use =DATEVALUE(A2) when A2 contains a recognizable date string, then format the result as a date. Ambiguous strings depend on the spreadsheet's locale, so verify an example where the day exceeds twelve. If the cell already contains a numeric date value, it does not need this text-conversion step.
Use =DAY(A2) when A2 contains a real date. For September 21, 2026, the result is 21. This is not the weekday number or a count of elapsed days. If entering a date directly in the formula, use DATE(2026,9,21) rather than unquoted slash-separated numbers that could be interpreted as division.
Use =MONTH(A2) to return a number from 1 through 12 for a valid date. September returns 9. Keep the original date if you later need to distinguish the same month in different years. Grouping only by month number combines all Januaries, all Februaries, and so on unless you also include a year.
Use =YEAR(A2) for a cell containing a valid date. The result is the calendar year, such as 2026. If the input is imported text, convert and verify it first instead of assuming its appearance proves it is a date. Calendar year can differ from an ISO week-based year near New Year's Day.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.