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.
Read More: Â VLOOKUP with Multiple Criteria Including Date Range in ExcelÂ
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.
Read More: Excel VLOOKUP with Multiple Criteria in Column and Row
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.
Read More: Â VLOOKUP with Multiple Criteria and Multiple ResultsÂ
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.
Download Practice Workbook
You can download our practice workbook from here for free!
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.
