# 6 Formulas to Lookup in Excel

We all know the famous star of Excel functions VLOOKUP. It is commonly used for looking up values with a unique Id. But this is not the only function that can be used for looking up values in Excel. There are many other functions and formulas that can be used to lookup value. In this article, I will introduce you with all these Excel lookup functions and formulas. Some are even better than the VLOOKUP function in Excel. So, read to the end.

### 1. The Excel VLOOKUP Function The first excel lookup function is of course the VLOOKUP function. This function is famous for a reason. We can use this function to do more than just a lookup. But the basic task of this function is to lookup values in the table, from left to right.

Syntax of VLOOKUP function: =VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])

Lookup_value:  The value by which you want to search in the first column of Table Array.

Table_array: The Table in which you want to look up/search

col_index_number:  The column number in Table Array from which you want to fetch results.

[range_lookup]: FALSE if you want to search for exact value, TRUE if you want an approximate match.

• Easy to use.
• Fast
• Multiple Use
• Best for looking up values in vertical order.

• It is only used for Vertical Lookup
• It returns only the first matched value.
• Static until used with MATCH function.
• Can’t lookup values from the left of the lookup value.

### 2. The Excel HLOOKUP Function The HLOOKUP function is the missing part of the VLOOKUP function. The HLOOKUP function is used to lookup values horizontally. In other words, when you want to lookup a value in Excel by matching value in columns and get values from rows, then we use the HLOOKUP function. This is exactly the apposite of the VLOOKUP function.

Syntax of HLOOKUP

=HLOOKUP(lookup value, table array, row index number, [range_lookup] )
• lookup value : The value you are looking for.
• Table Array : The table in which you are looking for the value.
• Row Index Number : The row number in the Table from which you want retrieve data.
• [range_lookup] : its the match type. 0 for the exact match and 1 for approximate match.

• It can lookup values horizontally.
• Easy to use.
• Multiple Use
• Fast

• It is only used for Horizontal Lookup
• It returns only the first matched value.
• Static until used with the MATCH function.
• Can’t lookup values above the lookup values in the table.

### 3. The INDEX-MATCH Lookup Formula Where VLOOKUP and HLOOKUP can’t reach, this formula can reach. This is the best lookup formula in Excel till Excel 2016 (XLOOKUP is on the way).

The Generic Formula of INDEX MATCH

=INDEX (Result_Range,MATCH(lookup_value,lookup range,0))

Result_Range: It is the range range from where you want to retrieve value.

Lookup_value: It is the value that you want to match.

Lookup_Range: It is range in which you want to match the lookup value.

• Can lookup in four directions. It can lookup values to the left and up of the lookup value.
• Dynamic.
• No need to define the row or column index.

• It may be difficult for new users.
• Uses two Excel functions in combination. Users need to understand the working of the INDEX and MATCH function.

### 4: Excel OFFSET-MATCH Lookup Formula This is another formula that can be used to lookup values dynamically. This excel lookup formula uses the OFFSET function as anchor function and MATCH as a feeder function. Using this formula, we can dynamically retrieve values from a table by looking up in rows and columns.

### Generic Formula, =OFFSET(StartCell,MATCH(RowLookupValue,RowLookupRange,0),MATCH(ColLookupValue,ColLookupRange,0))

StartCell: This is the starting cell of lookup Table. Let’s say if you want to lookup in range A2:A10, then the StartCell will be A1.

RowLookupValue: This is the lookup value that you want to find in rows below the StartCell.

RowLookupRange: This is the range in which you want to lookup the RowLookupValue. It is the range below StartCell (A2:A10).

ColLookupValue: This is the lookup value that you want to find in columns (headers).

ColLookupRange: This is the range in which you want to lookup the ColLookupValue. It is the range on the right hand side of StartCell (like B1:D1).

Advantages of this Excel lookup technique:

• Fast
• Can lookup horizontally and vertically.
• Dynamic

• Complex to some people.
• Need to understand the working of OFFSET function and MATCH function.

### 5: Excel LOOKUP Formula Multiple Values All of the above lookup formulas return the first found value from the array. If there are more than one match they will not return other matches. In that case, this formula comes into action to save the day. This formula returns all the matched values from the list, instead of the first match only.

This formula use INDEX, ROW, and IF functions as main functions. The IFERROR function can be used optionally to handle errors.

Generic Formula

{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}

Array: The range from where you want to fetch data.
lookup_value: Your lookup_value that you want to filter.
lookup_value_range: The range in which you want to filter lookup_value.
The first cell in lookup_value range: if your lookup_value range is \$A\$5:\$A\$100 then its \$A\$5.
Important: Everything should be absolute referenced. lookup_value can be relative according to requirement.
Enter it as an array formula. After writing formula hit CTRL+SHIFT+ENTER key to make it an array formula.

As you can see in the gif, it returns all the matches from the excel table.

• Returns with multiple matched values from the Excel Table.
• Dynamic

• It’s too complex for a new user to understand.
• Uses array formula
• Need to define the possible number of outputs and apply this formula as a multi-cell array formula (Not in Excel 2019 and 365).
• Slow.

## 6: VLOOKUP-CHOOSE Lookup Excel Formula So, most people say that it is not possible to lookup values from the left of the lookup value in Excel using VLOOKUP function. Well, I am sorry to say, but they are wrong. We can lookup to the left of the lookup value in Excel using VLOOKUP function with the help of the CHOOSE function.

Generic Formula:

= VLOOKUP ( lookup_value , CHOOSE ( { 1 , 2 } , lookup_range , req_range ) , 2 , 0)

lookup_value : value to look for

lookup_range : range, where to look lookup_value

req_range : range, where corresponding value is required

: second column, num representing the req_range

: look for the exact match

In this formula, we basically create a virtual table inside the formula using the CHOOSE function. The CHOOSE function creates a table of two columns. The first column contains the lookup range and the second column contains the result range.