Suppose you have a data range, and you want to know the id of the orders, and you want to get their relative product name from the left column. The normal VLOOKUP function will not work. So how could you deal with this task in Excel? Here, I can introduce you to other formulas to solve this problem. Let’s see the steps to get the left lookup with INDEX and MATCH functions in Excel.
Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Generic Formula:
- To get the corresponding left value from the right specific data, the following formula can help you.
Syntax Explanations:
- INDEX – The INDEX function returns the value at a given position in a range or array.
- MATCH – In Excel, this function will locate the position of a lookup value in a row, column, or table. Read more on the MATCH function.
- 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.
- Range – It represents the input ranges in your worksheet.
- ID – It is the criteria that want to get the values.
Example:
Refer to the below example. Here we will show how to get the details of the product based on its order id.
- First, we will give the input ranges in Column B to Column E.
- Here we need to find the details of the products of order id 1004.
- So, enter the formula in the cell H3, and you can see the formula in the formula bar section.
- Now, we can get the result in the selected cell.
Wrap-Up:
Hope you understood the simple steps to get the left lookup of given data by using the INDEX and MATCH functions in Excel. Found something that we’ve not covered? Stuck on something? Just drop it in the comment box below. Thanks for visiting Geek Excel!! and Excel Formulas!!
Top comments (0)