Sometimes you need to look up data that meets more than one criteria from more than one columns in a data set. Unfortunately, VLOOKUP on its own only handles a single piece of lookup data.
=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])
But we have a simple workaround to handle multiple criteria in multiple columns using VLOOKUP function. This workaround is helpful where you want to retrieve only the data that meets multiple criteria.
In short, we add a helper column or lookup column as a leftmost column (first column) of your data set or table_array and combine or concatenate the values of multiple columns that contain criteria values as a single lookup value by using concatenating operator (&). Thus, we have created a new data entry which is the unique combination of multiple values in the table. Finally, in the VLOOKUP function we also put together multiple criteria values as a single lookup_value argument by concatenating them by using concatenating operator, ampersand (&).
So, the generic syntax of VLOOKUP function to handle multiple criteria would be as follows;
In this article, you are going to learn how to use VLOOKUP with multiple criteria. Suppose you have a sales data of unique cigarette brands in different regions with their quantities sold and sales amounts, and you want to look up the sales amount of a unique brand in a specified region using VLOOKUP function. You have two criteria here to meet to get the desired results, Region and Brand.
As a workaround for VLOOKUP function, you need to introduce a Helper column or Lookup column as a first or leftmost column in dataset or table_array. In this helper column, you need to combine the values of Region and Brand columns as a single value by using concatenating operator ampersand (&).
Now, you need to look up your both criteria values as a combined single lookup value in this first column (Helper) to return the sales amount where both criteria meet. To do that you need to concatenate your both criteria values using an ampersand (&) as a single lookup_value in VLOOKUP function. As your 1st criteria are Region and 2nd criteria is Brand value, so your VLOOKUP formula would be as follows in active cell K2;
Using this formula pattern you can combine more than one criteria as a single lookup_value in VLOOKUP function and can easily return the value that meets these multiple criteria.