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

Get FREE Advanced Excel Exercises with Solutions!

Introducing helper columns to vertically lookup data always brings complexity. It also manipulates the raw data as additional data columns are introduced. For these reasons, you may not want to add a helper column to vlookup data. In this article, you will learn 5 ways to vlookup with multiple criteria without a helper column in Excel.


How to Vlookup with Multiple Criteria without a Helper Column in Excel: 5 Ways

1. Combine the VLOOKUP and CHOOSE Functions to Vlookup with Multiple Criteria in Excel

The VLOOKUP function and the CHOOSE function create a virtual table. Thus you don’t need any helper columns to vlookup through data.

To vlookup using these functions,

❶ Select cell G9 first.

❷ Then insert the following formula:

=VLOOKUP(G7&G8,CHOOSE({1,2},B5:B12&C5:C12,D5:D12),2,FALSE)

Here,

  • G7&G8 are cells where the criteria are.
  • CHOOSE({1,2} creates a virtual function of two columns. The first column has the merged content of the cells G7&G8. The second column has the values from cell D5:D12.
  • B5:B12 is the range to look for the content of cell G7.
  • C5:C12 is the range to look for the content of cell G8.
  • D5:D12 is the range to extract the output.
  • 2 is the column index number of the virtual table to extract the output.
  • FALSE denotes the exact match.

❸ After that hit the ENTER button to get the vlookup result.

💡 If you are using older versions of Microsoft Excel except Office 365, then press CTRL + SHIFT + ENTER to insert the formula.

Combine the VLOOKUP and CHOOSE Functions to Vlookup with Multiple Criteria without a Helper Column in Excel

Read More:  VLOOKUP with Multiple Criteria Including Date Range in Excel 


2. Combine the INDEX and MATCH Functions to Vlookup without a Helper Column in Excel

If you want to bypass the creation of a virtual table to vlookup, then follow the steps below:

❶ Click on cell G9 to insert the following formula:

=INDEX(D5:D12,MATCH(G7&G8,B5:B12&C5:C12,0))

Here,

  • D5:D12 is the range of cells to extract the output of vlookup.
  • G7&G8 is the cell where the multiple criteria are stated.
  • B5:B12&C5:C12 is the range of cells to look for the criteria.
  • MATCH(G7&G8,B5:B12&C5:C12,0) searches for matches in between B5:B12&C5:C12 with G7&G8
  • INDEX(D5:D12,MATCH(G7&G8,B5:B12&C5:C12,0)) searches for matches in between B5:B12&C5:C12 with G7&G8 respectively and returns the output from D5:D12.

❷ Now press the ENTER button to get the vlookup value.

Combine the INDEX and MATCH Functions to Vlookup with Multiple Criteria without a Helper Column in Excel

Read More: Excel VLOOKUP with Multiple Criteria in Horizontal & Vertical Way


3. Combine the VLOOKUP and IF Functions to Vlookup with Multiple Criteria

You can join the VLOOKUP and IF functions to vlookup with multiple criteria without using a helper column. For that,

❶ Select a cell first.

❷ Then insert the following formula:

=VLOOKUP(G7, IF(C5:C12=G8, B5:D12, ""), 3, FALSE)

In this formula,

  • G7 contains the first criterion.
  • C5:C12=G8 looks for the value stored in cell G8 within the range C5:C12.
  • B5:D12 is the table array.
  • IF(C5:C12=G8, B5:D12, “”) checks if there’s a match between G8 and C5:C12. If there’s a match it returns the value from B5:D12. Otherwise, it returns nothing.
  • 3 is the column index number of the table array to get the output.
  • FALSE refers exact match.
  • VLOOKUP(G7, IF(C5:C12=G8, B5:D12, “”), 3, FALSE) checks if there’s a match between G8 and C5:C12. If there’s a match it returns the value from the 3rd column of B5:D12. Otherwise, it returns a null value.

❸ Finally, hit the ENTER button to get the output vlookup value.

Join the VLOOKUP and IF Functions to Vlookup with Multiple Criteria without a Helper Column in Excel

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


4. Use the SUMIFS Function to Vlookup with Multiple Criteria in Excel

You can use the SUMIFS function to vlookup at multiple criteria without using any helper columns. Now follow the steps below to get the steps:

❶ First insert the following formula in cell G9.

=SUMIFS(D:D,B:B,G7,C:C,G8)

Here,

  • D:D refers to the cell range to extract the output value.
  • B:B refers to a source data column.
  • G7 contains the first criteria.
  • C:C refers to another source data column.
  • G8 contains the second criteria.

❷ After that hit the ENTER button.

Thus you will get the vlookup data in cell G9.

Read More: VLOOKUP with Multiple Criteria and Multiple Results 


5. Use the XLOOKUP Function to Vlookup with Multiple Criteria without a Helper Column

If you are using Microsoft Office 365, then you can use the XLOOKUP function to vlookup with multiple criteria without the help of a helper column in Excel.

Now the steps below to learn to use this function:

❶ First of all, select a cell.

❷ Now insert the following formula in that cell:

=XLOOKUP(1, (G7=B5:B12) * (G8=C5:C12), D5:D12)

In this formula,

  • G7=B5:B12 looks for the content of G7 in the range B5:B12.
  • G8=C5:C12 looks for the content of G8 in the range C5:C12.
  • D5:D12 is the cell range to get the output value.
  • 1 is the lookup value.

❸ Finally, hit the ENTER button to get the vlookup data in cell G9.

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


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


Conclusion

To sum up, we have discussed 5 ways to vlookup with multiple criteria without a helper column in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.


Related Articles


<< Go Back to VLOOKUP with Multiple Criteria | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo