So far we have learned how to perform a lookup with the VLOOKUP function. In this tutorial, we are going to see how to find the two-way approximate match lookup based on multiple criteria in Excel by using the INDEX and MATCH functions. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
General Formula:
You can use the below formula to perform multiple criteria lookup.
=INDEX(range,MATCH(B1,IF(data1=B2,data2),1),MATCH(B3,range,1))
Syntax Explanations:
- INDEX – In Excel, the INDEX function will return the value at a given position in a range or array.
- MATCH – This function helps to locate the position of a lookup value in a row, column, or table. Read more on the MATCH function.
- IF – The Excel** IF function** return one value for a TRUE result, and another for a FALSE result.
- Range – It represents the input ranges.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
Practical Example:
Refer to the below example image. Here we will show how to look up a value based on multiple criteria.
- First, we will enter the input ranges in Column B to Column E.
- Then, apply the above-given formula to the formula bar section.
- Finally, we will get the results as per the given below.
Bottom-line:
From this guide, we have described the simple formulas used to find the two-way approximate match lookup based on multiple criteria in Excel. Hope you like it. If you have any doubts , feel free to share them with us. Keep learning on Geek Excel!! *and Excel Formulas *!!
Top comments (0)