Excel Functions – Lookup & Reference in Excel

Date:

Share post:

Lookup and reference functions in Excel are used to find and return values from a range of cells. These functions can be used to perform a variety of tasks, such as finding the price of an item by its part number, or finding the name of a customer by their phone number.

Introduction

Learn all about lookup & reference functions in Excel, such as VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE.

Vlookup

The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify.

1. Insert the VLOOKUP function shown below.

Lookup & Reference in Excel

Explanation: the VLOOKUP function looks for the ID (104) in the leftmost column of the range $E$4:$G$7 and returns the value in the same row from the third column (third argument is set to 3). The fourth argument is set to FALSE to return an exact match or a #N/A error if not found.

2. Drag the VLOOKUP function in cell B2 down to cell B11.

Lookup & Reference in Excel

Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays the same, while the relative reference (A2) changes to A3, A4, A5, etc. Visit our page about the VLOOKUP function for much more information and many examples.

Hlookup

In a similar way, you can use the HLOOKUP (Horizontal lookup) function.

Lookup & Reference in Excel - Hlookup Function

Match

The MATCH function returns the position of a value in a given range.

Match Function

Explanation: Yellow found at position 3 in the range E4:E7. The third argument is optional. Set this argument to 0 to return the position of the value that is exactly equal to lookup_value (A2) or a #N/A error if not found. Use INDEX and MATCH in Excel and impress your boss.

See also  Excel VBA - Create a Macro in Excel

Index

The INDEX function below returns a specific value in a two-dimensional range.

Index Function Two Dimensional Range

Explanation: 92 found at the intersection of row 3 and column 2 in the range E4:F7.

The INDEX function below returns a specific value in a one-dimensional range.

Index Function One Dimensional Range

Explanation: 97 found at position 3 in the range E4:E7. Use INDEX and MATCH in Excel and impress your boss.

Choose

The CHOOSE function returns a value from a list of values, based on a position number.

Choose Function

Explanation: Boat found at position 3.

Conclusion

Here are some additional tips for using lookup and reference functions:

  • Use absolute references when you need to prevent the cell references in a formula from changing when you copy the formula to other cells.
  • Use named ranges to make your formulas easier to read and maintain.
  • MATCH function can be used to find the position of a value in a range, even if the value is not an exact match.
  • Use the INDEX function to return a specific value from a range, even if the value is not in the first row or column of the range.
  • Use the CHOOSE function to return a value from a list of values, based on a position number.

By following these tips, you can use lookup and reference functions to their full potential in Excel.

Next Chapter: Financial Functions

LEAVE A REPLY

Please enter your comment!
Please enter your name here

spot_img

Related articles

Excel Example – Subtract in Excel

Subtract There's no SUBTRACT function in Excel. However, there are several ways to subtract numbers in Excel. Are you...

Excel Example – Most Used Functions in Excel

Most Used Functions Let's check out the 10 most used Excel functions. Visit our section about functions for detailed...

Excel Example – ROW function in Excel

The ROW function in Excel returns the row number of a reference. For example, =ROW(C4) returns 4. The...

Excel Example – Move Columns in Excel

To move columns in Excel, use the shift key or use Insert Cut Cells. You can also change...