Farihat
Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. In cases of data tracking, there are significant examples where one needs to Vlookup data using multiple criteria. Using multiple criteria, you can combine functions to generate a result. Without a helper column in Excel, you can Vlookup multiple criteria in five ways. It applies to both adjacent and non-adjacent columns and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.
📁 Download Excel File
Download the Excel file below.
Learn to Vlookup for Multiple Criteria Without Helper Column in Excel with These 5 Suitable Approaches
The article is going to introduce five ways to Vlookup multiple criteria without a helper column in Excel. The following discussion will cover the uses of the VLOOKUP function in two ways with the help of the IF and the CHOOSE functions. Rest three methods involve the XLOOKUP, the SUMIFS, the INDEX, and the MATCH functions. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.
1. Using VLOOKUP and IF Functions
You can use the VLOOKUP function for multiple criteria in Excel without a helper column. To fulfill the condition, you should use the IF function.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell G7.
- Second, enter the following formula here.
- Third, hit the Enter key.
- Fourth, you will get your required data as output.
🔨 Formula Breakdown
VLOOKUP(G5, IF(C6:C9=G6, B6:D9, “”), 3, FALSE)
👉 Here, IF fulfills the condition if G6 is found within the cell range C6:C9 from the B6:D9 cell range.
👉 Also, VLOOKUP returns value with respect to G5.
Finally, we get 80.
2. Merging VLOOKUP and CHOOSE Functions
The VLOOKUP function is a vertically lookup function in Excel. To Vlookup values from a dataset, multiple criteria can operate simultaneously, even without a helper column. For that, you need to merge the CHOOSE function along with the VLOOKUP function.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell G7.
- Next, enter the following formula here.
- Then, hit the Enter key.
- Finally, you will be able to Vlookup values from a dataset, multiple criteria can operate simultaneously, even without a helper column.
🔨 Formula Breakdown
VLOOKUP(G5&G6,CHOOSE({1,2},B6:B9&C6:C9,D6:D9),2,FALSE)
👉 Here, CHOOSE brings index number as 1.
👉 Also, VLOOKUP returns for G5&G6 from cell range B6:B9&C6:C9.
Finally, we get 79.
3. Applying XLOOKUP Function
You can apply the XLOOKUP function to Vlookup values for multiple criteria without helper column in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell G7.
- Now, enter the following formula here.
- Next, hit the Enter key.
- Finally, you will be able to Vlookup values from a dataset, where multiple criteria can operate simultaneously, even without a helper column.
4. Utilizing SUMIFS Function
You can utilize the SUMIFS function to Vlookup values from a dataset, where multiple criteria can operate simultaneously, even without a helper column. This function is only applicable for numerical values.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select cell G7.
- Then, enter the following formula here.
- Then, hit the Enter key.
- Therefore, Excel will be able to Vlookup values from a dataset, where multiple criteria can operate simultaneously, even without a helper column.
5. Combining INDEX and MATCH Functions
The INDEX function and the MATCH function can work miracle as a combination. You can make use of it to Vlookup values from a dataset, where multiple criteria can operate simultaneously, even without a helper column.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell G7.
- Next, enter the following formula here.
- Next, hit the Enter key.
- Moreover, Excel will automatically format all the duplicate data by rows from the table like the following image.
🔨 Formula Breakdown
INDEX(D6:D9,MATCH(G5&G6,B6:B9&C6:C9,0))
👉 Here, MATCH searched G5&G6 amidst range B6:B9&C6:C9.
👉 Also, INDEX gives you a reference at the intersection of rows or columns on a chosen range by the MATCH function.
Finally, we get 72.
How to Use VLOOKUP for Multiple Columns in Excel
The VLOOKUP function is a multipurpose lookup function. You can use it to lookup for multiple columns in Excel in same worksheet or different ones.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell G7.
- Second, enter the following formula here.
- Third, hit the Enter key.
- Fourth, you will get all your columns from the lookup values in Excel.
📄 Important Notes
🖊️ All the data formats must be formatted properly. Otherwise, Excel may auto-format to something inappropriate for the user.
🖊️ You can modify the formula if you want something specific if Excel finds a match.
🖊️ If your Enter key isn’t working for formula operations, you should try the Ctrl+Shift+Enter keys to operate the formulas in case of older versions of Microsoft Excel.
📝 Takeaways from This Article
The article lets the readers understand the variety of options available to Vlookup values from a dataset, where multiple criteria can operate simultaneously, even without a helper column in Excel.
📌 Primarily, you can Vlookup multiple criteria without helper column using the VLOOKUP function. Adding the IF function to it make the calculation precise by adding conditions.
📌 Also, you can use the CHOOSE and the VLOOKUP functions altogether.
📌 A simple formula using the XLOOKUP function can be of help as well. It will let you lookup values across horizontally or vertically.
📌 You can use the SUMIFS function as well.
📌 Here, Excel can also utilize a combination of the INDEX and the MATCH functions in Excel.
Conclusion
To Vlookup values from a dataset, where multiple criteria can operate simultaneously, even without a helper column in Excel, five methods are common. You can use uses of the VLOOKUP function in two ways with the help of the IF and the CHOOSE functions. Rest three methods involve the XLOOKUP, the SUMIFS, the INDEX, and the MATCH functions. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
