In the following dataset, we’ll now create two drop-down lists for smartphone brands and model numbers in Cells D15 and D16 respectively. Rather, you’ll select the smartphone brand and model number from the drop-down lists and the VLOOKUP function will display the price for the corresponding smartphone model. After creating the drop-down lists, you don’t have to input the criteria manually anymore. We can also create the drop-down lists for the VLOOKUP function. Use of Drop-Down Lists as Multiple Criteria in VLOOKUP Read More: Excel VLOOKUP to Return Multiple Values VerticallyĦ. 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. 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 Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)Ĥ. 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. =VLOOKUP(D15&D16,B5:G13,MATCH(B18,B4:G4,0),FALSE)Īfter pressing Enter, you’ll see the price of the specified smartphone model.

Using vlookup excel for mac