123 useful answers · Page 2 of 6
Work through formulas, formatting, sorting, and everyday data tasks.
Search this topicWith categories in A and amounts in B, =AVERAGEIF(A2:A50,"Delivery",B2:B50) averages amounts on Delivery rows. The label range and amount range must describe the same records. Text-formatted amounts can be excluded unexpectedly. Check the qualifying count and sum alongside the average so you can tell whether missing or nonnumeric records are affecting the result.
In a version supporting MINIFS, enter =MINIFS(B2:B50,A2:A50,"East") to find the smallest B value on East rows. A zero result can also mean no records matched, so first check =COUNTIF(A2:A50,"East"). Do not report zero as a real minimum without confirming a match. Keep both ranges the same size and store the compared values as numbers.
AVERAGE divides by the count of numeric values, not the total number of worksheet rows. Empty cells and text in a referenced range are omitted; numeric zeros are included. For 10, a blank and 20, =AVERAGE(A1:A3) gives 15. Count the numeric entries and inspect imported numbers stored as text before deciding that the function is wrong.
Enter =MIN(B2:B50) in a summary cell outside that range. MIN examines numeric values and ignores text or blanks in referenced cells. Zero is a number, so it can be the minimum. If all entries are missing or nonnumeric, a zero result is not evidence of a real observed minimum. Check =COUNT(B2:B50) before interpreting an empty dataset.
Use =MAX(B2:B50) to return the largest numeric value in the selected data rows. Keep the summary cell outside the range and exclude any existing totals that are not individual observations. Negative values work normally: the maximum of -8 and -3 is -3. Check imported values for text formatting if an obviously larger number appears to be ignored.
If a score is in B2 and the pass mark is 70, use =IF(B2>=70,"Pass","Fail"). To leave missing scores unclassified, use =IF(B2="","",IF(B2>=70,"Pass","Fail")). The comparison includes exactly 70. Test a blank cell, 69, 70 and 71 before filling down, and keep numeric scores separate from explanatory notes.
Suppose B2 is a quantity and C2 is the normal unit price. =IF(B2>=10,B2*C2*0.9,B2*C2) applies a 10% reduction when quantity reaches ten. Write each calculation separately first, then combine them with IF. Check quantities 9 and 10 to confirm the threshold. The formula is a worked example; replace the condition with your actual pricing rule.
Wrap a working formula in IFERROR, such as =IFERROR(B2/C2,"Check inputs"). It substitutes the message for any error the calculation produces. Diagnose the original error first: IFERROR can hide broken references or misspelled functions as easily as missing inputs. Use a message that invites correction rather than replacing every failure with a misleading numeric zero.
Use IFNA when a missing match is expected but other errors should remain visible. For example, =IFNA(VLOOKUP(E2,A2:B50,2,FALSE),"Not listed") replaces #N/A while leaving errors such as #REF! available to investigate. Confirm that the identifier truly is absent before accepting the message; extra spaces or a number stored as text can also cause a failed lookup.
Use AND to combine checks, such as =AND(B2>=18,C2="Yes"). It returns TRUE only when both checks pass. To produce labels, use =IF(AND(B2>=18,C2="Yes"),"Ready","Review"). Test each possible combination of the two inputs. Keep missing values distinguishable from a genuine No so an incomplete row does not accidentally pass your rule.
Use =OR(A2="Red",A2="Blue") to check whether A2 contains either allowed label. To return words, wrap it in =IF(OR(A2="Red",A2="Blue"),"Included","Other"). Repeat the complete comparison on each side of OR; writing only the second label is not a valid equivalent. Test both allowed labels and one label that should fail.
Use =NOT(B2) when B2 contains a logical TRUE or FALSE value. It can also reverse a comparison, such as =NOT(A2="Closed"). That example returns TRUE for blanks as well as other statuses, so add an explicit nonblank check if incomplete rows should be excluded. Do not assume a displayed word is a logical value if it was imported as text.
Use IFS with the highest threshold first: =IFS(B2>=90,"High",B2>=70,"Medium",TRUE,"Low"). Excel returns the result for the first true condition, so reversing the order could mislabel a high value as Medium. The final TRUE supplies a default. Add a separate blank check if the list includes missing scores, then test each threshold and values just below it.
For codes in A2, try =SWITCH(A2,"N","New","P","Processing","C","Complete","Unknown code"). SWITCH compares the input with each listed code and returns the corresponding label. The final value handles unexpected inputs. It is convenient for a short fixed list; use a lookup table when people need to maintain many codes without editing formulas.
In Excel supporting XLOOKUP, use =XLOOKUP(E2,A2:A50,C2:C50,"Not found"). E2 is the requested code, A holds codes, and C holds prices. XLOOKUP uses an exact match by default. Make sure lookup codes are unique or decide which duplicate should win. Keep codes consistently formatted, especially when they contain leading zeros, and test a missing code as well as a known one.
Use =XLOOKUP(E2,A2:A50,C2:C50,"Not found",0,-1) to search from the bottom of the code range upward. The -1 search mode selects the last matching row, not necessarily the newest date. If newest is your goal, first ensure row order represents chronology. Test a code that appears twice with different return values to confirm which record wins.
Put FALSE in the fourth argument: =VLOOKUP(E2,A2:C50,3,FALSE). The lookup value must appear in the first column of that table, and 3 selects its third column. Leaving off the final argument enables approximate matching, which is unsuitable for many item-code searches. Verify one existing identifier and one nonexistent identifier; the missing exact match should produce #N/A.
VLOOKUP uses a numeric position within its selected table. In =VLOOKUP(E2,A2:C50,3,FALSE), the 3 means the third table column. Inserting or rearranging columns can make that number point at different information. Recheck the selected table and return-column position after layout changes. XLOOKUP or INDEX with MATCH can make the intended return range more explicit in a maintained workbook.
Use =INDEX(B2:B20,3) to return the third value in that range, which is cell B4. INDEX counts positions inside the supplied range rather than worksheet row numbers. With a two-dimensional range, supply both row and column positions, such as =INDEX(B2:D20,3,2). Check the range's starting cell when a returned result appears one row off.
Use =MATCH(E2,A2:A50,0) for the position of an exact match. If the match is in A2, the result is 1; if it is in A5, the result is 4. It does not return the worksheet row number. The final zero is important for an unsorted identifier list. Missing values return #N/A, which you can investigate before applying error handling.
In a version supporting XMATCH, use =XMATCH(E2,A2:A50,0,-1). It searches backward for an exact match and returns the position within A2:A50. To retrieve another column at that position, wrap it in INDEX, for example =INDEX(C2:C50,XMATCH(E2,A2:A50,0,-1)). Ensure both ranges describe the same records; position 1 must refer to the same row in each.
In dynamic-array Excel, use =FILTER(A2:C50,B2:B50="Open","No open records"). The result expands into nearby cells and changes when the source values change. Put the formula outside the source table with enough empty space below and to its right. This produces a separate result; it does not hide or delete source rows. Check that the condition range matches the table's height.
Use =FILTER(A2:C50,(B2:B50="Open")*(C2:C50>100),"No matches") in Excel with dynamic arrays. Multiplication combines the two Boolean tests as AND, so both must be true. Keep parentheses around each comparison. Test a row satisfying only one condition to confirm it is excluded. Leave the output area clear so the returned rows can spill into adjacent cells.
Use =UNIQUE(A2:A50) in Excel with dynamic arrays. It returns one copy of each distinct value without removing anything from the source. Filter out blanks first if they are unwanted, and clean inconsistent spacing before deduplicating. The results need empty cells below the formula. A distinct list is different from a list of values that occur only once.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.