Category Archives: Microsoft Excel

VLOOKUP using multiple requirements

Microsoft Excel

Generic formula

=VLOOKUP(val1&val2,data,column,0)

Summary

The VLOOKUP function is not able to manage multiple criteria in native. If you do are in control of the source data You can make use of the aid column to connect multiple fields togetherand then use the fields in the same way as multiple criteria in VLOOKUP. In the example, Column B is a helper column that combines names of the first and last and VLOOKUP uses the exact same method thing to make a lookup. The formula used in I6 is:

=VLOOKUP(I4&I5,data,4,0)

in which „data“ is the name of the range where „data“ is the named range B4:F104

It is important to note that this method is straightforward, but not sufficient. If you want a more effective solution that doesn’t require the helper column, look up this formula that is that is based in Boolean reasoning. Consider a simpler solution based on INDEX and the MATCH and XLOOKUP.

Explanation

In the case shown below, we’re looking up employees‘ departments and groups using VLOOKUP by comparing first and last names.

One drawback that is present in VLOOKUP can be that it only takes care of one specific condition that is the lookup_value. This is compared to the first column in the table. This makes it hard to make use of VLOOKUP to discover the value that is which is based on more than one selection of criteria. If you do have control over the data you are using and you want to include a helper column which joins two additional fields. Then, you can give VLOOKUP an option to lookup a value that is exactly the similar.

The helper column is used to join field values from the columns used as criteria and must be the primary column of the table. Within the VLOOKUP function the value for the lookup is created as well by joining the criteria.

In the example the formula used I6 is: I6 is:

=VLOOKUP(I4&I5,data,4,0)

When I4 along with I5 have been joined together, then we can:

=VLOOKUP(„JonVictor“,data,4,0)

VLOOKUP finds „JonVictor“ on the 5th row of „data“, and returns the value from the fourth column „Marketing“.

Set up

To create a multiple criteria VLOOKUP Follow these 3 steps:

  1. Include a helper column to combine (join) value from the columns you intend to use to meet your requirements.
  2. Create VLOOKUP to be able to identify an existing table that has one column called the Helper Column. The helper column should become the primary column of the table.
  3. To find the value to lookup to be a lookup value, join the numbers in the identical sequence to be able to match values from the column that helps.
  4. Be sure VLOOKUP has been set up to do an exact matching.