Excel VLOOKUP with Multiple Criteria in Horizontal & Vertical Way

Excel is extremely powerful for large data handling and calculations. Sometimes, we need to look up some values horizontally and vertically according to different criteria from large datasets. In this article, I will show you 3 suitable ways to Use VLOOKUP for horizontal and vertical Searches with multiple criteria in Excel.

Though the VLOOKUP function is dedicated to vertical lookup with a single criterion only, we can use it to look up values horizontally with more than one criterion using some techniques. So let’s explore those.


Download Practice Workbook

You can download our practice workbook from here for free!


3 Ways to Use VLOOKUP for Horizontal and Vertical Search with Multiple Criteria in Excel

Say, you have a dataset of 10 sales persons’ first and last names and their respective departments. Now, you want to find the department of a certain employee by giving his first and last name. You can use any of the ways below to lookup for multiple criteria in a horizontal and vertical direction to find the desired person’s department.

Sample Dataset to Vlookup Multiple Criteria Horizontal and Vertical


1. Use a Helper Column to Left to Match Multiple Criteria with VLOOKUP

You can use the VLOOKUP function with a helper column to find your desired person’s department vertically. Follow the steps below to do this.

📌 Steps:

  • First and foremost, create a helper column on the left side of your dataset.

Helper Column

  • Afterward, click on cell B5.
  • Subsequently, insert the following formula with AND function and hit the Enter button.
=AND(C5=$D$16,D5=$D$17)

Using AND Function to Get Helper Column Value

  • For all the other helper data, place your cursor in the bottom right position of cell B5.
  • Following, a black fill handle will appear.
  • Subsequently, drag the fill handle below to copy the same formula for all the cells below.

Use Fill Handle Feature

  • As a result, we will get all the helper column data.

Helper Column Data

  • At this time, click on cell D18 and insert the following formula.
=VLOOKUP(TRUE,B5:E14,4,FALSE)
  • Subsequently, press the Enter button.

Using VLOOKUP Function to Vlookup Multiple Criteria in Horizontal and Vertical Direction

Thus, you will be able to look up your desired data in the dataset with multiple criteria horizontally and vertically.

Read More: How to Use VLOOKUP with Multiple Criteria in Different Columns


2. Apply VLOOKUP Function with Multiple Criteria Using Ampersand (&) Operator with Helper Column

Besides, you can also use the Ampersand (&) operator with the VLOOKUP function to find specific values vertically for multiple criteria. Follow the steps below to accomplish this.

📌 Steps:

  • First, click on cell B5 and insert the following formula in your helper column cell.
=C5&D5
  • Following, press the Enter button.

Using Ampersand (&) Operator to Get Helper Column Data

  • At this time, place your cursor in the bottom right position of cell B5 and drag the appeared black fill handle downward to copy the same formula for all the cells below.

Fill Handle Feature

  • As a result, you will get all the helper column data to your needs.

Helper Column Values

  • Afterward, click on cell D18 and insert the formula below.
=VLOOKUP(D16&D17,B5:E14,4,FALSE)
  • Following, press the Enter button.

Using VLOOKUP Function to Find Multiple Criteria in Horizontal and Vertical Direction

As a result, you will get your desired person’s department looked up.

Read More: Vlookup with Multiple Criteria without a Helper Column in Excel (5 Ways)


3. Use Helper Row and Combine TRANSPOSE Function with VLOOKUP for Horizontal Lookup

Moreover, you can use a helper row and combine the TRANSPOSE function to lookup for multiple criteria horizontally. Go through the steps below to do this.

📌 Steps:

  • At the very beginning, at the Helper Row, click on cell C4.
  • Subsequently, insert the formula below and press the Enter button.
=C5&C6

Use Ampersand (&) Operator to Get Helper Row Data

  • Afterward, place your cursor in the bottom right position of cell C4.
  • Subsequently, drag the fill handle rightward upon its arrival.

Fill Handle Feature

  • As a result, you will get all the data of the helper row.

Helper Row Data

  • Last but not least, click on cell C11 and insert the following formula.
=VLOOKUP(C9&C10,TRANSPOSE(C4:L7),4,FALSE)
  • Following, hit the Enter button.

Combine VLOOKUP & TRANSPOSE Functions Lookup Multiple Criteria Horizontally

Consequently, you will get the department for Peter Parker.

Read More: Excel VLOOKUP with Multiple Criteria in Column and Row


2 Alternative Formulas to VLOOKUP for Horizontal and Vertical Lookup with Multiple Criteria in Excel

Here, we will provide some alternatives to the VLOOKUP function for horizontal and vertical lookup with multiple criteria.

1. INDEX-MATCH Formula for Vertical and Horizontal Lookup with Multiple Criteria

Lookup Vertically:

Apart from the previous ways described above, you can also combine INDEX and MATCH functions to lookup for horizontal and vertical lookup with multiple criteria. Follow the steps below to do this.

📌 Steps:

  • Initially, click on cell C18 and insert the following formula.
=INDEX(D5:D14,MATCH(1,(B5:B14=C16)*(C5:C14=C17),0))

🔎 Formula Breakdown:

  • MATCH(1,(B5:B14=C16)*(C5:C14=C17)

This function will return the row index number where the C16 cell’s value is in the B5:B14 range and the C17 cell’s value is in the C5:C14 range.
Result: 8

  • INDEX(D5:D14,MATCH(1,(B5:B14=C16)*(C5:C14=C17),0))

This function returns the value from the D5:D14 cells for the previous row index result.
Result: Production

  • Subsequently, hit the Enter button.

Combine INDEX & MATCH Functions to Vlookup Multiple Criteria

As a result, you can get the desired result for your desired salesperson.


Lookup Horizontally:

You can also combine the INDEX and MATCH functions to lookup for multiple criteria horizontally by following the steps below.

📌 Steps:

  • First, click on cell C10.
  • Subsequently, insert the following formula and press the Enter button.
=INDEX(C6:L6,MATCH(1,(C4:L4=C8)*(C5:L5=C9),0))

Combine INDEX & MATCH Functions to Lookup Multiple Criteria Horizontally

Thus, you can get the desired person’s department by horizontal lookup.

Read More: VLOOKUP with Multiple Criteria Including Date Range in Excel (2 Ways)


2. Using XLOOKUP Function to Lookup Vertically and Horizontally with Multiple Criteria

The XLOOKUP function has some extra advantages over the VLOOKUP function when looking up data. Because the VLOOKUP function can not lookup for values to the left columns of the lookup value. On the contrary, the XLOOKUP function can look up values on any side of the data.

Lookup Vertically:

Follow the steps below to accomplish this.

📌 Steps:

  • First and foremost, click on cell C18 and insert the following formula.
=XLOOKUP(C16&C17,B5:B14&C5:C14,D5:D14)
  • Subsequently, hit the Enter button.

Using XLOOKUP Function to Vlookup Multiple Criteria

Lookup Horizontally:

Besides, you can use the XLOOKUP function to look up horizontally for multiple criteria. Go through the steps below to do this.

📌 Steps:

  • Initially, click on cell C10.
  • Afterward, insert the following formula.
=XLOOKUP(C8&C9,C4:L4&C5:L5,C6:L6)
  • Subsequently, hit the Enter button.

Using XLOOKUP Function to Lookup Multiple Criteria Horizontally

Thus, you can get your desired result.


Conclusion

So, in this article, I have shown you 3 suitable ways to use the VLOOKUP function for multiple criteria in horizontal and vertical directions. I suggest you read the full article carefully and apply the knowledge to your needs. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to learn more things about Excel! Have a nice day! Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo