SERP priority hub

Top 20 Excel Search Queries (High-Value English Topics)

Curated map of the twenty highest-value English Excel searches — VLOOKUP, XLOOKUP, SUMIFS, #N/A, pivot totals, and more — with direct links to in-depth guides.

How to use this SERP priority hub

  • Each link below maps to a high-volume English Excel search with a dedicated in-depth page on Excel.Directory.
  • Start with P1 lookup and error topics if you are fixing #N/A or choosing between VLOOKUP and XLOOKUP.
  • Move to P2 conditional totals when SUMIF or COUNTIF returns zero or wrong aggregates.
  • Finish with P3 hubs and P4 reporting when you need modern dynamic arrays or pivot reconciliation.

Cross-topic troubleshooting checklist

  • Confirm exact match (FALSE or 0) on ID and code lookups before wrapping formulas in IFERROR.
  • Clean text with TRIM; compare types with EXACT or a helper column when lookups miss.
  • Lock table references with $ before filling formulas down long columns.
  • Verify Excel version: XLOOKUP, FILTER, and UNIQUE require Microsoft 365 or Excel 2021 desktop.

In-depth coverage for each priority query

  • functions/vlookup: VLOOKUP is still the most searched Excel lookup. This guide covers exact-match syntax, real table layouts, when to switch to XLOOKUP, and how to fix #N/A without hiding real data problems.
  • functions/xlookup: XLOOKUP is the default recommendation for new workbooks in Microsoft 365. Learn the six arguments that matter, how match_mode changes results, and when your team still needs VLOOKUP.
  • compare/xlookup-vs-vlookup: Both functions solve lookups, but they differ in column rules, error handling, and Excel version support. Use this comparison when migrating legacy models or training a mixed-version team.
  • problems/vlookup-returning-na: #N/A means Excel did not find an exact match (with FALSE) or could not use approximate match correctly. Fix the data and logic first — then use IFNA only for display.
  • blog/index-match-excel-guide: INDEX MATCH combines position lookup with value retrieval. It predates XLOOKUP but remains essential for legacy files, two-way tables, and models where row and column logic are calculated separately.
  • errors/na: #N/A means “value not available.” In lookup formulas it almost always means no match was found. This hub focuses on VLOOKUP, XLOOKUP, and INDEX MATCH — the source of most #N/A searches.
  • compare/index-match-vs-xlookup: INDEX MATCH and XLOOKUP both escape VLOOKUP’s left-column rule. XLOOKUP is easier to read in new files; INDEX MATCH remains the standard for older Excel and some two-way designs.
  • blog/vlookup-excel-tutorial: This tutorial walks from blank sheet to a working VLOOKUP in about 15 minutes. You will build three mini tables and practice exact match before touching XLOOKUP.
  • functions/sumif: SUMIF totals values where a single criterion is true — one region, one status, one product line. Master criteria operators and cell references before moving to SUMIFS.
  • blog/sumifs-excel-examples: SUMIFS powers sales dashboards, payroll summaries, and operations KPIs. These examples use a Data sheet plus a Setup sheet so criteria stay easy to change without editing formulas.
  • compare/sumif-vs-sumifs: SUMIF is for one rule; SUMIFS is for multiple AND conditions. The argument order changes — that swap causes more errors than the logic itself.
  • problems/sumif-not-working: SUMIF problems are usually criteria syntax, text vs number, or misaligned ranges — not a broken Excel install. Walk through these checks before rewriting the whole report.
  • functions/countif: COUNTIF answers “how many?” for one rule — open tickets, orders over $1k, cells containing a word. Pair it with SUMIFS on the same dashboard for count + dollars.
  • guides/excel-lookup-functions-guide: Excel lookup functions connect keys to attributes — prices, names, tax rates. This hub orders them by skill level and links to comparisons, tutorials, and error fixes.
  • functions/filter: FILTER returns every row that meets your conditions and spills the result. It is the M365 answer to “show me all East orders over $500” without copying formulas down.
  • problems/xlookup-not-finding-value: When XLOOKUP fails, check match_mode, array alignment, and data types before changing the return column. if_not_found is for display — not a substitute for a valid match.
  • functions/if: IF evaluates a test and returns one of two outcomes. For three or more branches, IFS is clearer than deep nested IFs — especially for grading, tiers, and status labels.
  • blog/xlookup-excel-tutorial: This tutorial assumes you know VLOOKUP basics and want a modern replacement. You will map old arguments to XLOOKUP and test left lookup plus if_not_found in one sheet.
  • blog/pivot-table-mistakes: When a Pivot total disagrees with SUMIFS or your expectation, the source data — not the Pivot UI — is guilty 80% of the time. Use this audit before rebuilding the report.
  • functions/countifs: COUNTIFS counts rows where every criteria pair is true. It mirrors SUMIFS logic but returns a count — ideal for volume KPIs next to dollar totals on the same dashboard.

FAQ across priority Excel searches

  • What does FALSE mean in VLOOKUP? FALSE (or 0) forces an exact match. Use it for IDs, SKUs, and codes. TRUE is approximate match and requires a sorted first column.
  • Why must the lookup column be first in the table? VLOOKUP only searches the leftmost column of table_array. Rearrange columns or switch to XLOOKUP if the key is not leftmost.
  • VLOOKUP vs XLOOKUP for a new file? Use XLOOKUP in Excel 2021/M365 for left lookups and cleaner syntax. Keep VLOOKUP when the workbook must open in Excel 2019.
  • Is XLOOKUP in Excel 2019? No on desktop Excel 2019. It is available in Excel 2021 and Microsoft 365 subscriptions.
  • What is match_mode in XLOOKUP? 0 = exact match (default for IDs). 2 = wildcard. -1 and 1 are binary search modes and require sorted lookup columns.
  • Can XLOOKUP replace IFERROR(VLOOKUP(...))? Yes — use the if_not_found argument after the lookup logic is correct. Do not use it to mask a broken match.
  • Should I still learn VLOOKUP in 2026? Yes. Legacy files, job interviews, and mixed-version teams still rely on it daily.
  • Is XLOOKUP faster than VLOOKUP? Performance is similar on typical business tables. Avoid full-column references on millions of rows regardless of function.
  • Can I mix both in one workbook? Yes during migration. Test XLOOKUP on a copy until results match VLOOKUP on sample keys.
  • VLOOKUP #N/A but the value looks identical? Hidden spaces, non-printing characters, or text vs number are the top causes. Use TRIM and VALUE.
  • Should I use IFERROR before fixing #N/A? No. IFERROR hides audit problems. Fix the match, then add IFNA for user-friendly display.
  • VLOOKUP #N/A on some rows only? Those keys are missing from the first column or fail exact match. Use MATCH to list missing keys.
  • Why use INDEX MATCH instead of VLOOKUP? INDEX MATCH can return values from any column position and supports two-way lookups without restructuring the table.
  • What does MATCH return? The relative position (row or column number) of a value in a range. INDEX uses that position to return the actual value.
  • INDEX MATCH vs XLOOKUP for new files? Prefer XLOOKUP for one-way lookups in M365. Keep INDEX MATCH for two-way models or Excel versions without XLOOKUP.
  • How do I hide #N/A in Excel? Use IFNA for lookups (=IFNA(VLOOKUP(...),"")) or XLOOKUP if_not_found. Fix the underlying match when possible first.
  • Does #N/A mean my formula is wrong? It means the formula ran but could not return a value — usually no match in a lookup. The syntax may still be correct.
  • Why #N/A in one cell but not the row above? The lookup value or criteria changed. Compare TRIM, data types, and whether the key exists in the source table.
  • Is INDEX MATCH obsolete? No. It is still common in enterprise models and interview tests.
  • Which is easier to audit? XLOOKUP for one-way lookups. INDEX MATCH when row/column logic is split across helper cells.
  • Can I convert INDEX MATCH to XLOOKUP automatically? Map MATCH position to XLOOKUP’s lookup_array/return_array. Test two-way models manually — they often stay INDEX MATCH.
  • How long does it take to learn VLOOKUP? Most users build a working formula in 15–30 minutes with a simple four-column table.
  • What is col_index_num? The column number within table_array to return, counting from the left. Column D in A:D is 4.
  • Can I practice without sample data? Type 10 rows of fake IDs and prices — structure matters more than real company data.
  • What is the argument order for SUMIF? SUMIF(criteria_range, criteria, sum_range). Note SUMIFS puts sum_range first — do not mix them up.
  • Does SUMIF sum only visible filtered rows? No. SUMIF includes hidden rows. Use SUBTOTAL on visible cells or SUMIFS on a helper column for filter-aware totals.
  • Can SUMIF criteria reference another cell? Yes. Use concatenation: =SUMIF(A:A,">"&F1,B:B).
  • Why does SUMIFS return 0? At least one criteria pair fails. Test each condition in isolation on a helper column.
  • How many criteria can SUMIFS handle? Up to 127 range/criteria pairs in modern Excel.
  • SUMIFS vs Pivot Table for this report? SUMIFS for fixed dashboard tiles; Pivot for exploratory analysis. See [pivot mistakes](/blog/pivot-table-mistakes/).
  • When is SUMIF enough? Exactly one condition — one region, one status, one rep.
  • Can SUMIFS reference different sheets? Yes if all ranges are the same size and shape.
  • SUMIF returns wrong total with filters applied? SUMIF ignores AutoFilter. Use SUBTOTAL or filter-aware helpers.
  • SUMIF returns 0 but values look correct? Check exact text match, date serial numbers, and that sum_range aligns row-for-row with criteria_range.
  • SUMIF double-counts after refresh? Duplicate rows in source data — dedupe or use COUNTIFS to audit keys first.
  • SUMIF with dates from CSV? Import may leave dates as text. Use DATEVALUE or Text to Columns on General.
  • Is COUNTIF case-sensitive? No. COUNTIF ignores case. Check spaces and wildcards instead.
  • How do I count cells greater than a value? =COUNTIF(range,">100") or ">"&cell for a dynamic threshold.
  • COUNTIF vs COUNTIFS? Use COUNTIFS when two or more conditions must all be true.
  • What is the best lookup function in Excel? XLOOKUP for new M365 one-way lookups; VLOOKUP for legacy; INDEX MATCH for two-way; FILTER when you need every match.
  • Do I still need VLOOKUP if I have XLOOKUP? Yes when files must open in Excel 2019 or when auditing legacy models.
  • Lookup returns wrong row but not #N/A? Approximate match or unsorted data with binary search — switch to exact match (FALSE / match_mode 0).
  • Does FILTER work in Excel 2019? No. FILTER requires Excel 2021 or Microsoft 365 with dynamic arrays.
  • What is the if_empty argument? Returns custom text when no rows match instead of #CALC!.
  • FILTER vs Excel AutoFilter? AutoFilter is manual UI. FILTER is formula-driven and updates when data changes.
  • XLOOKUP #N/A but VLOOKUP worked? Often match_mode, different lookup column, or if_not_found masking an old IFERROR pattern.
  • XLOOKUP returns #SPILL? Different issue — destination blocked. FILTER and XLOOKUP can both spill; clear the range.
  • Horizontal XLOOKUP not working? Pass a row as lookup_array; return_array must align column-for-column.
  • How many nested IFs can Excel handle? Many, but readability collapses after 2–3 levels. Use IFS instead.
  • Can IF return a number and text? Yes. TRUE and FALSE branches can be different types.
  • IF vs IFS vs SWITCH? IF for one test; IFS for ordered tiers; SWITCH for exact match to a list of values.
  • Do I need Office 365 for this tutorial? You need Excel 2021 or Microsoft 365. Excel 2019 cannot run XLOOKUP.
  • Should I delete all VLOOKUP formulas? Only after testing XLOOKUP on a copy and confirming stakeholders can open the file.
  • XLOOKUP tutorial vs function page? Tutorial is step-by-step. Function page is syntax, errors, and reference detail.
  • Why doesn't Pivot match my SUM column? Pivot may use Count instead of Sum — check Value Field Settings. Or source numbers are text.
  • Calculated field vs calculated item? Fields operate on data columns; items operate inside a field. Wrong choice changes totals.
  • Pivot total changed after refresh? New rows outside the old source range — convert to Table or update Change Data Source.
  • How is COUNTIFS different from COUNTIF? COUNTIF allows one condition. COUNTIFS requires all listed pairs to match (AND logic).
  • Can COUNTIFS do OR logic? Not in one formula — add two COUNTIFS and subtract overlap, or use FILTER in M365.
  • COUNTIFS with multiple regions? List each region as separate formulas and sum, or use criteria in cells with SUMPRODUCT patterns.