The basic theory of using CONCAT strings to form a Name Range for lookup value and then do Average in excel formula is as follows:
- CONCAT is a function that combines two or more text strings into one. For example, =CONCAT(“Hello”,” “,“World”) returns “Hello World”.
- A Name Range is a named reference to a group of cells or a constant value. For example, you can name the range A1:A10 as “Data” and use it in formulas instead of typing A1:A10. This makes your formulas easier to read and maintain.
- A lookup value is a value that you want to find in a table or a range. For example, if you have a table of product names and prices, you can use a product name as a lookup value to find its price.
- VLOOKUP is a function that searches for a lookup value in the first column of a table or a range and returns a value from another column in the same row. For example, =VLOOKUP(“Apple”,A2:B10,2,FALSE) searches for “Apple” in the first column of the range A2:B10 and returns the value from the second column in the same row.
- AVERAGE is a function that calculates the arithmetic mean of a set of numbers. For example, =AVERAGE(1,2,3,4,5) returns 3.
The procedures of using CONCAT strings to form a Name Range for lookup value and then do Average in excel formula are as follows:
- First, you need to create a Name Range that contains the values that you want to concatenate and use as lookup values. For example, if you have a drop-down list in column A and you want to concatenate the values in column A and B, you can select the range A1:B10 and name it as “TESTING”.
- Second, you need to use the CONCAT function to combine the values in the Name Range and use the INDIRECT function to convert the text result into a reference. For example, =INDIRECT(CONCAT(A2,B2)) returns the reference to the cell that matches the concatenated value of A2 and B2 in the Name Range “TESTING”.
- Third, you need to use the VLOOKUP function to search for the reference returned by the INDIRECT function in a table or a range that contains the values that you want to average. For example, if you have a table of product names and quantities in Sheet3, you can use =VLOOKUP(INDIRECT(CONCAT(A2,B2)),Sheet3!A1:B10,2,FALSE) to find the quantity of the product that matches the concatenated value of A2 and B2 in the Name Range “TESTING”.
- Fourth, you need to use the AVERAGE function to calculate the average of the values returned by the VLOOKUP function. For example, if you want to average the quantities of the products that match the values in the Name Range “TESTING”, you can use =AVERAGE(VLOOKUP(INDIRECT(CONCAT(A2,B2)),Sheet3!A1:B10,2,FALSE)).
A comprehensive explanation of using CONCAT strings to form a Name Range for lookup value and then do Average in excel formula is as follows:
- The purpose of this formula is to calculate the average quantity of a set of products based on their names and categories, which are selected from a drop-down list in column A and entered manually in column B.
- The formula uses the CONCAT function to combine the values in column A and B into one text string, which represents the name and category of a product. For example, if A2 is “Fruit” and B2 is “Apple”, the formula will concatenate them into “FruitApple”.
- The formula uses the INDIRECT function to convert the text string into a reference to a cell in the Name Range “TESTING”, which contains the same values as the drop-down list and the manual entries. For example, if the Name Range “TESTING” is A1:B10, the formula will look for the cell that contains “FruitApple” and return its reference, such as A3.
- The formula uses the VLOOKUP function to search for the reference in the first column of a table or a range in Sheet3, which contains the product names and quantities. For example, if Sheet3 has a table in A1:B10, the formula will look for the reference A3 in the first column and return the value from the second column in the same row, such as 50.
- The formula uses the AVERAGE function to calculate the arithmetic mean of the values returned by the VLOOKUP function. For example, if the formula is entered in C2 and copied down to C10, it will average the quantities of the products that match the values in the Name Range “TESTING”.
A scenario to give a detailed example with real numbers is as follows:
- Suppose you have a workbook with three sheets: Sheet1, Sheet2, and Sheet3.
- In Sheet1, you have a drop-down list in column A that allows you to select from four options: “Fruit”, “Vegetable”, “Meat”, and “Dairy”. In column B, you can enter the specific name of the product, such as “Apple”, “Carrot”, “Beef”, or “Milk”.
- In Sheet2, you have a Name Range called “TESTING” that contains the same values as the drop-down list and the manual entries in Sheet1. For example, if you select “Fruit” in A2 and enter “Apple” in B2 in Sheet1, you will have “FruitApple” in A2 in Sheet2. The Name Range “TESTING” covers the range A1:B10 in Sheet2.
- In Sheet3, you have a table in A1:B10 that contains the product names and quantities. For example, A2 has “FruitApple” and B2 has 50, which means there are 50 units of apple in the fruit category. The table has the same values as the Name Range “TESTING” in the first column, but different values in the second column.
- In Sheet1, you want to calculate the average quantity of the products that match the values in the Name Range “TESTING”. You enter the following formula in C2 and copy it down to C10: =AVERAGE(VLOOKUP(INDIRECT(CONCAT(A2,B2)),Sheet3!A1:B10,2,FALSE))
- The formula works as follows:
- In C2, the formula concatenates the values in A2 and B2, which are “Fruit” and “Apple”, into “FruitApple”.
- The formula converts the text “FruitApple” into a reference to the cell that contains the same value in the Name Range “TESTING”, which is A2 in Sheet2.
- The formula searches for the reference A2 in the first column of the table in Sheet3 and returns the value from the second column in the same row, which is 50.
- The formula averages the value 50 and returns it in C2.
- The formula repeats the same steps for the other cells in column C, using the values in column A and B as inputs.
- The result is a column of average quantities of the products that match the values in the Name Range “TESTING”.
An excel table to explain the scenario is as follows:
Table
| Sheet1 | A | B | C |
|---|---|---|---|
| 1 | Category | Product | Average Quantity |
| 2 | Fruit | Apple | 50 |
| 3 | Vegetable | Carrot | 40 |
| 4 | Meat | Beef | 30 |
| 5 | Dairy | Milk | 20 |
| 6 | Fruit | Banana | 60 |
| 7 | Vegetable | Tomato | 50 |
| 8 | Meat | Chicken | 40 |
| 9 | Dairy | Cheese | 30 |
| 10 | Fruit | Orange | 70 |
Table
| Sheet2 | A | B |
|---|---|---|
| 1 | TESTING | |
| 2 | FruitApple | |
| 3 | VegetableCarrot | |
| 4 | MeatBeef | |
| 5 | DairyMilk | |
| 6 | FruitBanana | |
| 7 | VegetableTomato | |
| 8 | MeatChicken | |
| 9 | DairyCheese | |
| 10 | FruitOrange |
Table
| Sheet3 | A | B |
|---|---|---|
| 1 | Product | Quantity |
| 2 | FruitApple | 50 |
| 3 | VegetableCarrot | 40 |
| 4 | MeatBeef | 30 |
| 5 | DairyMilk | 20 |
| 6 | FruitBanana | 60 |
| 7 | VegetableTomato | 50 |
| 8 | MeatChicken | 40 |
| 9 | DairyCheese | 30 |
| 10 | FruitOrange | 70 |
The result of the scenario is that the formula in column C in Sheet1 returns the average quantity of the products that match the values in the Name Range “TESTING” in Sheet2, which are based on the values in column A and B in Sheet1. The formula uses the table in Sheet3 as the source of the quantities.
Other approaches that are available are as follows:
- You can use the CONCATENATE function instead of the CONCAT function, which does the same thing but allows you to enter more than 255 text strings as arguments. For example, =CONCATENATE(A2,B2) is equivalent to =CONCAT(A2,B2).
- You can use the SUMPRODUCT and COUNTIF functions instead of the VLOOKUP and AVERAGE functions, which can perform the same calculation without using a Name Range or a reference. For example, =SUMPRODUCT((Sheet3!A1:A10=CONCAT(A2,B2))*Sheet3!B1:B10)/COUNTIF(Sheet3!A1:A10,CONCAT(A2,B2)) is equivalent to =AVERAGE(VLOOKUP(INDIRECT(CONCAT(A2,B2)),Sheet3!A1:B10,2,FALSE)).
- You can use the SUMIFS and COUNTIFS functions instead of the VLOOKUP and AVERAGE functions, which can also perform the same calculation without using a Name Range or a reference. For example, =SUMIFS(Sheet3!B1:B10,Sheet3!A1:A10,CONCAT(A2,B2))/COUNTIFS(Sheet3!A1:A10,CONCAT(A2,B2)) is equivalent to =AVERAGE(VLOOKUP(INDIRECT(CONCAT(A2,B2)),Sheet3!A1:B10,2,FALSE)).
