Vlookup is one of the most used and powerful formulas for excel. This will make your life easier when you have to search for various string positions and values in large data tables. In this article I will show you some ways to use vlookup excel formula for multiple criteria and multiple matches.

How do we differentiate between data types depending on their appearance in excel

Usually we have to pay attention to the type of values ​​we will use the vlookup formula. In the sense that we cannot look up numbers in a table with text and no text in the table with number information. We must first transform the type of data used in vlookup into the type contained in the search table. This mission is done by using the formulas TRIM – to transform numbers into string and VALUE to transform numbers into string data.

To ease our situation, excel displays the string data type different from the numbers type. The numbers will always be aligned to the right, and the text or string data will be displayed with default alignment to the left. Of course we can align the text column to the center or to the right, but we can’t align the number column. It will remain aligned to the right no matter how much we want to change this.

For cases when the string cells will be aligned to the right, excel puts a green marker in the upper left corner. Thus we realize at a glance that we are dealing with another type of data. You can see this in the image below.

Vlookup formula with multiple criteria with examples

For the situations that we have to perform a search based on several value criteria, we will have to perform a preliminary preparation of the source table. More specifically, we will have to form a special column by concatenating the criteria according to which we will do the search with vlookup.

To make a concatenation we can use the CONCAT formula, or more simply, use the character “&” between the searched criteria.

Let’s imagine that we have to look in the table below for a car based on personal criteria and preferences.

Using vlookup to identify values based on multiple criteria involves building a helper column. Preferably to the left of the table we are looking for because vlookup works from left to right. To do this, use the “&” character to concatenate the 4 criteria, as seen in the image below.

Because concatenation automatically converts number data to string data, we don’t have to worry about problems caused by type differences.

Now we can use the vlookup formula from excel to search for a red Jeep Wrangler from 2019 using the expression from the definition of this formula. As can be seen in the example below, finding the price (the 6th column) for these multiple criteria is a breeze.