Then do a countif to see how often John's mum is an account holder in your H column put in column E row 2 "=C2&D2" and pull down to get a bunch of cells which look like "John's MumAccount Holder" (all contained in the one adjacent cell) It is very similar to the VLOOKUP but it is able to work well with arrays in the initial values.Vlookup won't work with the way this data is laid out, because it will always return the first result when it finds the person it wants then it will quit the job without looking any further down the columns.Ī good work around is to make column E concatenate the two values together (i.e. In other words, if in our table the values "pear" and "apple" are repeated, we cannot sum up all the pears and apples. It is worth noting that the main disadvantage of the VLOOKUP function is the inability to select several identical initial values in the query. You can execute the formula with a cyclic array only through a combination of hot keys: CTRL+SHIFT+ENTER.ĭownload example VLOOKUP function in two tables Also, click on the function icon then manually write and search the formula.
If you enter the curly braces manually in the formula string, this will not work. We need to understand the various terms associated with the use of vlookup and sum in excel to find the total of values that meet the criteria. The entire process is performed cyclically thanks to an array of functions as indicated by the curly brackets in the formula bar. Now enter the name of the product in cell A11 and in cell B11 we get the sales amount in the first quarter for this product.Ĭomparison of two tables in Excel is performed by the VLOOKUP function and as soon as the matching of the requested data is determined, their values are immediately substituted for summation by the SUM function.
We fill in the arguments of the function.In the appeared dialog box on the category field, select the "Lookup and Reference" from the drop-down list, and then specify the function below. Or you can press the hotkey combination SHIFT+F3. To do this, click on the "fx" button, which is at the beginning of the formula bar. You can also enter =VLOOKUP() using the "Insert Function". Choose "FORMULAS" - "Lookup and Reference" - "VLOOKUP".We move to the cell of the second table under the name of the column "Price".
Nearby is another table, which will search in the first table using the criteria “name of the product” and get the value of the corresponding price. Although the Excel lookup functions can seem quite straightforward, it’s very easy to get the wrong answer if you don’t fully understand how they work. For example, if the table consists of two columns: "Product name" and "Price". Excel has a range of functions that you can use to achieve this including VLOOKUP() and HLOOKUP() and the more flexible, but slightly more complicated, combination of INDEX() and MATCH(). The VLOOKUP function is used to retrieve data from an Excel table using certain search criteria. How the VLOOKUP function works in Excel: an example