Multiple Criteria for VLOOKUP

The VLOOKUP function in Microsoft Excel allows for searching a lookup value and returning the specified row’s corresponding value.

Frequently, there are various criteria needed when you use Excel’s VLOOKUP.

How You Can Use VLOOKUP with Various Criteria

Check if you have information on the students’ names, type of exam, and math scores like the one shown below:


With this, you can use the VLOOKUP function so you can get every student’s math score.

However, it may become quite challenging.

Some people may argue that it would be better to have the data set restructured using the Pivot Table.

However, most of the time, you may get stuck with the information that you have that the pivot table may not become an option.

For these cases, our tutorial can be advantageous.

There are now a couple of ways you can have the lookup value using multiple criteria with VLOOKUP.

  • Use the CHOOSE function
  • Use the Helper Column

Use a Helper Column for VLOOKUP with various criteria

Using the helper columns can be significantly advantageous in Excel compared to various formulas because of the following:

  • It can make things faster than different functions with prominent and noticeable data sets.
  • Using it can make anyone easily understand what is happening in the worksheet.

Having an array of formulas is excellent as there are many great things you can do with them.

However, it may be best to save them for any special occasion whenever every other option can no longer help.

When it comes to the question, you need the helper column to create a unique qualifier.

You can have this special qualifier used so you can look up the correct value.

For example, the data has three Matt, but there can only be a single combination of the Matt and Mid-Term and Matt and Unit Test.

These are the steps that you need to take:

  • Have a Helper Column inserted between the B and C columns.
  • Make use of the formula =A2&” |” &B2 in the helper column. For every instance, it can make individual qualifiers like what is shown here:
 
  • Have this formula used in G3=VLOOKUP($F3&”|”&G$2,$C$2:$D$19,2,0)
  • Copy this for every cell.

How this works

Individual qualifiers are created for every instance that there is an exam and a name. For the VLOOKUP function that we used, they had the lookup value modified to $F3&” |” &G$2 for both of the lookup criteria to combine and become used as one lookup value. As an example, in G2, the VLOOKUP function’s lookup value is Matt|Unit Test. You can then have this lookup value used so you can have the score from C2:D19.

Clarifications on some queries:

There may be specific queries that you would think about frequently. We will try to have them answered here:

Why did I have the helper column inserted between columns B and C instead of the extreme left?

Having the helper column inserted to the extreme left is not harmful.

In case you don’t want to end up tampering with any of the original information, this is what you need to do.

That is what I did as it allows me to use fewer cells in the function VLOOKUP.

Instead of the table array having four columns, I can only manage two columns.

Why did I use the | symbol as I joined the two criteria?

There may be some possible, but sporadic conditions wherein there are two different criteria that provide the same result when combined.

Here is an example that is quite simple:

If A2 and A3 are not the same, just like B2 and B3, the combinations will be the same.

However, using a separator, the cross (D2 and D3) will become different.

Since there may not be a size that can fit all, some people may choose not to have any helper column used as they use VLOOKUP with various criteria.

Check out this column method that you can apply to your excel files:

VLOOKUP-Multiple-Criteria.xlsx

The Use of the CHOOSE Function in VLOOKUP with Multiple Criteria

The use of various formulas instead of the helper columns can save the real estate in your worksheet that the performance can be just as good whenever it gets used less in any workbook.

As you consider the same data set that you used beforehand, this is the formula that can provide you with that result:

=VLOOKUP($E3&”|”&F$2,CHOOSE({1,2},$A$2:$A$19&”|”&$B$2:$B$19,$C$2:$C$19),2,0)

Instead of just using Enter, an array formula has Control + Shift + Enter used with it.

How is this working?

The helper column concept is what the formula is also using.

The difference between them is that instead of having the helper column placed in the worksheet, people can have it considered virtual helper data that can be a segment of the formula.

Here is what I mean when I mention virtual helper data:

In this illustration, as I click on the formula’s CHOOSE part and press F9. Here, the result that the formula CHOOSE would provide is shown:

Here is the result:

{“Matt|Unit Test,” 91;” Bob|Unit Test,” 52;……}

It comes with an array wherein a comma represents the next cell in the same row.

The semicolon is a representation that the next column has the following information.

Because of that, the formula makes 2 data columns: one is with the score while the other has a unique identifier.

As you are using the function VLOOKUP, it will simply search for the first column’s value and provides the score that corresponds to it.

Leave a Comment