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

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.


Download Practice Workbook

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


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

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: How to Apply VLOOKUP with Multiple Criteria Using the CHOOSE Function


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: Example of VLOOKUP with Multiple IF Condition in Excel (9 Criteria)


Similar Readings


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: Excel VLOOKUP with Multiple Criteria in Column and Row


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: XLOOKUP with Multiple Criteria in Excel (4 Easy Ways)


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. And please visit our website ExcelDemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo