- #How to use vlookup in excel with example how to#
- #How to use vlookup in excel with example full#
- #How to use vlookup in excel with example free#
Since we’re using a similar dataset here, the required formula in Cell C18 to pull out the price of the Samsung S21 Ultra will be:Īfter pressing Enter, you’ll get two model numbers of Apple and Xiaomi smartphones.Īs the VLOOKUP function always extracts the first matched data, in this example, only the first model numbers of Apple and Xiaomi brands have appeared as return values.Ħ. Read more: IF and VLOOKUP Nested Function We have to use the IF function here to define the lookup array for the VLOOKUP function.
There is another method to avoid a helper column while using the VLOOKUP function with multiple criteria in Excel. Combining VLOOKUP with IF Function to Join Multiple Criteria For example, if you type Chipset in Cell B18, the embedded formula in Cell C18 will be shown the chipset name for the specified smartphone model.Ĥ. So, now if you change the output type in Cell B18, the corresponding result in Cell C18 will be updated at once. And this column number is then assigned to the third argument (col_num_index) of the VLOOKUP function. In this formula, the MATCH function looks for the value present in Cell B18 in the array of B4:G4 and then returns the column number. The VLOOKUP formula will continue to work normally.=VLOOKUP(D15&D16,B5:G13,MATCH(B18,B4:G4,0),FALSE)Īfter pressing Enter, you’ll see the price of the specified smartphone model.
#How to use vlookup in excel with example free#
You can also easily change the table to handle a new department.įinally, because VLOOKUP is set to exact match, lookup values do not need to be sorted-you're free to arrange the values as you like. What's nice about this approach is that you can easily change the grouping just by changing the table. When I copy this formula down, we get a group for each department. Column index is 2 and match type is 0 (or false), to force an exact match. Next, I'll enter the VLOOKUP formula we need to do the grouping.įor the lookup value, we need Department, and for the look_up table I'll use the named range group_map. Finally, let's use a named range for the table to make the VLOOKUP formula easier to read and copy. Now we need to add a group for each department.
#How to use vlookup in excel with example full#
When I click OK, I get a full set of unique values, and I can just copy these back into the table. Just copy the values to another worksheet, and use Remove Duplicates on the Data tab of the ribbon. This is a great-use case for Excel's built-in "remove duplicates" feature. Then we need a complete list of departments. With VLOOKUP, we need Department to be in the left-most column, and we need the lookup value to be on the right.
To do this, we need to first build the helper table. Well, you could use nested IF statements as we've covered in another video, and this works fine.īut another way to perform this same grouping is to use VLOOKUP with a "helper" table to map each department into the group you want.
What if you need to group these employees into groups A and B, by department, as shown in this table? For example, in this case, we have a list of employees and each employee is in one department. Sometimes you need to group values into discreet categories that don't exist in your data.
#How to use vlookup in excel with example how to#
In this video we'll look at how to use VLOOKUP to group data into specific categories.