Home
6 Formulas to Lookup in Excel

6 Formulas to Lookup in Excel

 moon0

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.

Advantages of  VLOOKUP:

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

Disadvantages:

  • 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.

Advantages of HLOOKUP Function:

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

Disadvantages:

  • 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.

Advantages of INDEX-MATCH lookup formula:

  • 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.

Disadvantages:

  • 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

Disadvantages:

  • 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.

Advantages:

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

Disadvantages:

  • 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

2 : second column, num representing the req_range

0 : 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.

Advantages of VLOOKUP-CHOOSE lookup formula:

  • You can lookup to the left of lookup value
  • Fast
  • Easy

Disadvantages:

  • The CHOOSE function is rarely used. Users need to understand its working.

So yeah guys, these are the different lookup functions and formulas. That is not all. There can be many more lookup formulas in Excel that can be created using different combinations of Excel formulas. If you have any special lookup techniques, please share in the comments section below. We will include it in our article with your name.

I hope it was helpful and informative. If you have any doubts regarding this article, ask me in the comments section below.

About The Author

moon