Excel VLOOKUP with Multiple Criteria in Horizontal & Vertical Way

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

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.

• 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)`

• 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.

• As a result, we will get all the 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.

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

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.

• 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.

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

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

As a result, you will get your desired personâ€™s department looked up.

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`

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

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

• 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.

Consequently, you will get the department for Peter Parker.

Read More: Â Vlookup with Multiple Criteria without a Helper Column in ExcelÂ

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

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.

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))`

Thus, you can get the desired personâ€™s department by horizontal lookup.

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.

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.

Thus, you can get your desired result.

Related Articles

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

Advanced Excel Exercises with Solutions PDF