Average VLOOKUP in Excel: How to Average Matching Rows

Formulas & functions · Updated November 21, 2026

To average values for every row that matches a lookup value, use AVERAGEIF rather than VLOOKUP. For example, if product names are in A2:A7, amounts are in B2:B7, and the product to find is in D2, enter =AVERAGEIF(A2:A7,D2,B2:B7). VLOOKUP returns a value from one matching row; it does not calculate the average of multiple matches.

What “average VLOOKUP” can mean

The phrase “average VLOOKUP” usually describes one of two different tasks. You might want the average amount recorded for a particular item, person, or category, even though it appears on several rows. Or you might want to look up one value for each of several keys and then average those results. VLOOKUP alone does neither averaging task: it looks for a key and returns a value from a specified column.

Choosing the right formula depends on what your rows represent. When repeated keys represent separate observations—such as several sales of the same product—average all matching rows with AVERAGEIF. If each key appears only once and you want the mean of several retrieved values, retrieve those values and average them. The distinction matters: using VLOOKUP on repeated keys silently gives one matching row, not a representative average.

How VLOOKUP works

VLOOKUP searches down the first column of a table and returns a value from another column on the same row. Its basic syntax is VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The column index is counted from the left edge of the selected table, not from the worksheet. For an exact match, set the final argument to FALSE.

For example, with product names in A2:A7, amounts in B2:B7, and the product to search for in D2, this formula returns the amount from the first matching row: =VLOOKUP(D2,A2:B7,2,FALSE). If the product appears multiple times, VLOOKUP still returns just one result—the first match it encounters from top to bottom. It does not continue through the rest of the table to collect or average the other amounts.

Leaving out the final argument or using TRUE requests an approximate match. Approximate matching is intended for ordered lookup values, typically arranged in ascending order, and returns a result based on the closest qualifying value. For ordinary labels, names, and product codes, use FALSE so Excel searches for an exact match.

Average all rows that match a key

AVERAGEIF is the straightforward choice when one criterion defines the records to include. It takes a range to test, a criterion, and the range of numbers to average. With product names in A2:A7, amounts in B2:B7, and the requested product in D2, use =AVERAGEIF(A2:A7,D2,B2:B7). If the matching amounts are 10, 14, and 18, the result is 14.

You can also put a fixed label directly in the formula. With product names in A2:A7 and amounts in B2:B7, this version averages the amounts for Widget: =AVERAGEIF(A2:A7,"Widget",B2:B7). A cell reference is usually more flexible: change D2 to another product and the result updates without editing the formula. The criterion range and average range should cover corresponding rows and have the same dimensions.

AVERAGEIF ignores text and blank cells in the average range, but includes numeric zeroes. That is useful when zero is a real recorded result, but it can affect an average if zero was used to represent missing data. Clean or flag missing values according to what they mean in your workbook rather than assuming that a blank and a zero are interchangeable.

Use more than one condition

Sometimes the key alone is not specific enough. You may need the average for a product in one region, a worker during one shift, or a category within a date range. AVERAGEIFS handles multiple conditions. Its first argument is the range to average; after that, provide a criteria range and criterion for each condition.

For example, suppose product names are in A2:A7, regions are in B2:B7, and sales amounts are in C2:C7. If D2 holds the product and E2 holds the region, use =AVERAGEIFS(C2:C7,A2:A7,D2,B2:B7,E2). Excel averages only the rows where both the product and region match. This is more dependable than combining several lookup tricks when each record needs to satisfy multiple filters.

AVERAGEIF and AVERAGEIFS support criteria such as greater than or equal to a value. With numbers in B2:B7 and a threshold in D2, this formula averages the numbers that meet or exceed that threshold: =AVERAGEIF(B2:B7,">="&D2,B2:B7). The ampersand joins the comparison operator to the value in D2. This can be useful for averages above a minimum score or within a numeric cutoff.

Average values retrieved for several keys

A different task is to look up a set of keys in a table where each key has one corresponding value, then average the returned values. In Microsoft 365, a VLOOKUP can process a range of lookup values as an array. With unique keys in A2:A7, their values in B2:B7, and the keys to retrieve in E2:E4, use =AVERAGE(VLOOKUP(E2:E4,A2:B7,2,FALSE)). If the three looked-up values are 10, 14, and 18, the average is 14.

This is not the same as averaging all rows that share a key. It assumes each requested key has a usable match, and that a key should contribute one value to the final average. If the source table has duplicates, VLOOKUP returns a result for the first matching row for each key. If any requested key is missing, the lookup can produce an error and prevent the average from returning a number. For a list of several keys where duplicates are meant to count as separate observations, use a method that includes all matching rows, such as AVERAGEIF for each criterion, rather than treating the keys as unique.

If your data is arranged as one row per key, it may be clearer to retrieve the results into a helper column and average that column. With the lookup keys in E2:E4 and the lookup table in A2:B7, enter =VLOOKUP(E2,$A$2:$B$7,2,FALSE) in F2 and fill it down through F4. Then, with the returned values in F2:F4, calculate their mean with =AVERAGE(F2:F4). The helper column makes it easy to inspect which value each key returned and spot a missing or unexpected match.

Common errors and data issues

If AVERAGEIF returns #DIV/0!, Excel did not find any qualifying numeric values to average. Check for spelling differences, extra spaces, mismatched data types, or a criterion that does not actually occur. A number stored as text may look like a number but not behave like one in calculations. Also confirm that the criteria range and average range line up row for row.

To show a more readable message when no qualifying numbers exist, use IFERROR. With product names in A2:A7, amounts in B2:B7, and the requested product in D2, use =IFERROR(AVERAGEIF(A2:A7,D2,B2:B7),"No numeric matches"). This handles an error result, but it does not repair bad source data. If the formula displays the message unexpectedly, investigate whether there are genuinely no matches or whether the data needs cleaning.

  • Extra spaces: “Widget” and “Widget ” can fail to match as expected. Cleaning imported text or using a helper column can prevent recurring mismatches.
  • Text versus numbers: IDs such as 0012 may be stored as text in one place and as a number in another. Keep key columns consistent, especially when leading zeroes matter.
  • Wildcards: AVERAGEIF criteria can use * for any sequence of characters and ? for one character. A criterion such as "East*" can include several labels, so check that it does not collect unintended records.
  • Case: Standard VLOOKUP and AVERAGEIF matching is not case-sensitive. If upper- and lowercase labels are intended to represent different keys, use a case-sensitive approach rather than assuming these formulas distinguish them.

VLOOKUP, AVERAGEIF, and alternatives

Use VLOOKUP when you want one corresponding result from a table, such as a department name or price associated with a unique ID. Use AVERAGEIF when you want the mean of every numeric value associated with one condition. Use AVERAGEIFS when every included row must satisfy multiple conditions. These functions answer related but different questions; replacing one with another does not change the meaning of the data.

XLOOKUP is a newer alternative for retrieving a single matching result in Microsoft 365 and Excel 2021 or later. It can search in a specified lookup range and return from a separate result range, so the return column does not have to sit to the right of the lookup column. Like VLOOKUP, XLOOKUP returns a lookup result; it is not a substitute for averaging all repeated records. AVERAGEIF and AVERAGEIFS remain the direct tools for conditional averages.

VLOOKUP’s column index can also be fragile if columns are inserted or moved inside its table range: the number may then point to a different field. That limitation matters when maintaining a workbook over time, but it does not change the key point about averages. A lookup function retrieves; an average function calculates across numeric observations.

A practical way to choose the formula

Before writing a formula, decide what one observation means in your sheet and whether repeated keys are intentional. Then choose a method that reflects that structure:

  1. One key, many records: Use AVERAGEIF to average all matching rows.
  2. Several conditions: Use AVERAGEIFS, with one criteria range and criterion for each required condition.
  3. One key, one record: Use VLOOKUP with FALSE when you need the corresponding value, not an average.
  4. Several unique keys, one value per key: Retrieve each value and average the results, checking for missing keys.
  5. Repeated keys that should all count: Do not rely on VLOOKUP’s first match; average the source records that meet the criteria.

Finally, test the result against a small, visible example. Filter the table to the matching records and calculate their mean independently, or add the values and divide by the count. This quick check can catch a range that starts or ends on the wrong row, a criterion that includes extra categories, or a zero that should not be included. Once the logic is correct, use cell references for criteria so the same formula can serve other products, regions, or periods.

Frequently Asked Questions

Can VLOOKUP return the average of duplicate matches?
No. VLOOKUP returns a value from the first matching row; it does not combine duplicate matches. Use AVERAGEIF for one condition or AVERAGEIFS for multiple conditions.

What formula averages values found by a lookup?
If you mean all source rows matching one key, use AVERAGEIF. If you mean one returned value for each of several unique keys, retrieve those values and average them. Be clear about whether duplicates should count individually.

Why does AVERAGEIF show #DIV/0!?
Usually there are no matching numeric cells to average. Check the criterion, text and number formats, spaces, and the alignment and size of the criteria and average ranges.

Should I use VLOOKUP with TRUE or FALSE?
For an exact match on a name, label, or ID, use FALSE. TRUE requests approximate matching and is appropriate only when the lookup data is ordered for that kind of search.

Can I average by product and region at the same time?
Yes. Use AVERAGEIFS, setting the sales or measurement range as its first argument and adding a criteria-range/criterion pair for the product and another for the region.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.