Working with Lookup Functions in Excel
Sep 14, 2020 • 12 Minute Read
Introduction
In this guide, you will learn how to work with lookup functions in Microsoft Excel 2019. This guide will help you to perform tasks like working with arrays of data, providing information about a range, returning the location of a given address or value, or looking up specific values.
There are a number of various lookup functions in Excel 2019:
- ADDRESS Function
- AREAS Function
- UNIQUE Function
- CHOOSE Function
- COLUMN Function
- COLUMNS Function
- INDEX Function
- MATCH Function
- VLOOKUP Function
- HLOOKUP Function
- LOOKUP Function
ADDRESS Function
The ADDRESS function returns "the address for a cell based on the given row and column numbers," according to Excel's documentation.
The most commonly used syntax for the ADDRESS function contains the row and column number:
=ADDRESS(row_number, column_number)
Consider a scenario in which you can implement the ADDRESS function:
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| Sr. No. | Row_Num | Column_Num | Abs_Num | A1 | Sheet_text | Address Formula | Result |
| 1 | 1 | 4 | =ADDRESS(B1, C1) | ? | |||
| 2 | 3 | 5 | 1 | =ADDRESS(B2,C2,D2) | ? | ||
| 3 | 2 | 1 | 2 | 1 | =ADDRESS(B3, C3, D3, E3) | ? | |
| 4 | 7 | 11 | 3 | 0 | sheet1 | =ADDRESS(B4, C4, D4, E4, F4) | ? |
| 5 | 18 | 12 | 4 | 1 | =ADDRESS(B5, C5, D5, E5) | ? |
After applying the formula, the result will be updated as shown below:
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| Sr. No. | Row_Num | Column_Num | Abs_Num | A1 | Sheet_text | Address Formula | Result |
| 1 | 1 | 4 | =ADDRESS(B1, C1) | $D$1 | |||
| 2 | 3 | 5 | 1 | =ADDRESS(B2,C2,D2) | $E$3 | ||
| 3 | 2 | 1 | 2 | 1 | =ADDRESS(B3, C3, D3, E3) | A$2 | |
| 4 | 7 | 11 | 3 | 0 | sheet1 | =ADDRESS(B4, C4, D4, E4, F4) | sheet1!R[7]C11 |
| 5 | 18 | 12 | 4 | 1 | =ADDRESS(B5, C5, D5, E5) | L18 |
AREAS Function
The AREAS function returns "the number of areas in a given reference where an area is a range of contiguous cells or a single cell," according to Excel's documentation.
It has the following syntax:
=AREAS(reference_to_cell(s)_areas)
Consider this data in which you can implement the AREAS function:
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| Sr. No. | Row_Num | Column_Num | Abs_Num | A1 | Sheet_text | AREA Formula | Result |
| 1 | 1 | 4 | =AREAS(B1:C1) | ? | |||
| 2 | 3 | 5 | 1 | =AREAS(B2:C2, D2) | ? | ||
| 3 | 2 | 1 | 2 | 1 | =AREAS(B3:E3) | ? | |
| 4 | 7 | 11 | 3 | 0 | sheet1 | =AREAS(B4:D4, E4, F4) | ? |
| 5 | 18 | 12 | 4 | 1 | =AREAS(B5:C5 B5) | ? |
After applying the formula, the result will be updated as shown below:
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| Sr. No. | Row_Num | Column_Num | Abs_Num | A1 | Sheet_text | AREA Formula | Result |
| 1 | 1 | 4 | =AREAS(B1:C1) | 1 | |||
| 2 | 3 | 5 | 1 | =AREAS(B2:C2, D2) | 2 | ||
| 3 | 2 | 1 | 2 | 1 | =AREAS(B3:E3) | 1 | |
| 4 | 7 | 11 | 3 | 0 | sheet1 | =AREAS(B4:D4, E4, F4) | 3 |
| 5 | 18 | 12 | 4 | 1 | =AREAS(B5:C5 B5) | 1 |
UNIQUE Function
The UNIQUE function "returns a list of unique values in a list or range," according to Excel's documentation.
It has the following syntax:
=UNIQUE(range_of_cells)
Consider the table below in which you have a few values in different rows:
| A | B | C |
|---|---|---|
| SR. NO. | VALUE | UNIQUE VALUES |
| 1 | 15 | |
| 2 | 18 | |
| 3 | 15 | |
| 4 | 16 | |
| 5 | 18 |
You can apply the =UNIQUE(B1:B5) function to get the unique values from the given list of values. The output will be as shown below:
| A | B | C |
|---|---|---|
| SR. NO. | VALUE | UNIQUE VALUES |
| 1 | 15 | 15 |
| 2 | 18 | 16 |
| 3 | 15 | 18 |
| 4 | 16 | |
| 5 | 18 |
CHOOSE Function
The CHOOSE function starts with an integer argument which acts as a selection index for the subsequent arguments.
It has the following syntax:
=CHOOSE(selection_index, arg1, arg2, arg3, ...)
Consider the following table:
| A | B | C |
|---|---|---|
| SR. NO. | Function | Output |
| 1 | =CHOOSE(4,"Monday", "Tuesday", "Wednesday", "Thursday", "Friday") | ? |
| 2 | =CHOOSE(2, 2, "Cat", 23, 321, "ABC") | ? |
| 3 | =CHOOSE(1,34,323,3221,1221) | ? |
In each row, after applying the function, the result will be updated as shown below:
| A | B | C |
|---|---|---|
| SR. NO. | Function | Output |
| 1 | =CHOOSE(4,"Monday", "Tuesday", "Wednesday", "Thursday", "Friday") | Thursday |
| 2 | =CHOOSE(2, 2, "Cat", 23, 321, "ABC") | Cat |
| 3 | =CHOOSE(1,34,323,3221,1221) | 34 |
COLUMN and COLUMNS Function
The COLUMN function "returns the column number of the given cell reference," whereas the COLUMNS function "returns the number of columns in the given array or reference," according to Excel's documentation.
Here's the syntax:
=COLUMN([address])
=COLUMNS([range])
Here's an example to understand their implementation. The function =COLUMN(B10) returns 2 as output because column B is the second column, whereas=COLUMNS(A1:E1) returns 5 as the output because the array is spread in five columns.
INDEX Function
The INDEX function "returns a value or the reference to a value from within a table or range," according to Excel's documentation.
It has the following syntax:
=INDEX(range, row_number, optional_column_number)
Consider the example below.
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| SR. NO. | NAME | SUBJECT | GRADE | RANK | FORMULA | OUTPUT |
| 1 | ALISHA | MATH | A | 2 | =INDEX(B1:D1, 1, 3) | ? |
| 2 | BEN | SCIENCE | A | 1 | =INDEX(B1:D5, 2, 1) | ? |
| 3 | CATHY | PHYSICS | D | 3 | =INDEX(B1:E5, 3, 2) | ? |
| 4 | DRAKE | CHEMISTRY | C | 4 | =INDEX(B1:E5, 2, 4) | ? |
| 5 | ELE | ECONOMY | B | 5 | =INDEX(B1:E5, 5, 4) | ? |
After applying the formula, the result will be updated as shown below:
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| SR. NO. | NAME | SUBJECT | GRADE | RANK | FORMULA | OUTPUT |
| 1 | ALISHA | MATH | A | 2 | =INDEX(B1:D1, 1, 3) | A |
| 2 | BEN | SCIENCE | A | 1 | =INDEX(B1:D5, 2, 1) | BEN |
| 3 | CATHY | PHYSICS | D | 3 | =INDEX(B1:E5, 3, 2) | PHYSICS |
| 4 | DRAKE | CHEMISTRY | C | 4 | =INDEX(B1:E5, 2, 4) | 1 |
| 5 | ELE | ECONOMY | B | 5 | =INDEX(B1:E5, 5, 4) | 5 |
MATCH Function
The MATCH function "searches a specific item in a range of cells, and returns the relative position of that item in the given range," according to Excel's documentation.
It has the following syntax:
=MATCH(input, range)
Consider the example below.
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| SR. NO. | NAME | MARKS | GRADE | FUNCTION | OUTPUT |
| 1 | ALISHA | 35 | F | =MATCH("BEN", B1:B5, 0) | ? |
| 2 | CATHY | 75 | D | =MATCH(94, C1:C5, 1) | ? |
| 3 | BEN | 78 | C | =MATCH("F", D1:D5, -1 ) | ? |
| 4 | ELE | 94 | B | =MATCH("A", D1:D5, -1 ) | ? |
| 5 | DRAKE | 95 | A | =MATCH("ELE", B1:B5, 0) | ? |
After applying the function, the result will be updated as shown below:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| SR. NO. | NAME | MARKS | GRADE | FUNCTION | OUTPUT |
| 1 | ALISHA | 35 | F | =MATCH("BEN", B1:B5, 0) | 3 |
| 2 | CATHY | 75 | D | =MATCH(94, C1:C5, 1) | 4 |
| 3 | BEN | 78 | C | =MATCH("F", D1:D5, -1 ) | 1 |
| 4 | ELE | 94 | B | =MATCH("A", D1:D5, -1 ) | 5 |
| 5 | DRAKE | 95 | A | =MATCH("ELE", B1:B5, 0) | 4 |
VLOOKUP Function
You can use the VLOOKUP function "when you need to find values in a table or a range by row," according to Excel's documentation. The VLOOKUP function has the following syntax:
=VLOOKUP(input, range, selection_index_from_column)
Consider a scenario where you can implement the VLOOKUP function. Say you have a database that contains information for all teachers in a class, as below:
If you want to know the D.O.B. for ID = 116, you would write the =vlookup(116,A2:E11,5,0) function and get the output 12-Aug-92, as shown below:
HLOOKUP Function
The HLOOKUP function works in a similar manner as the VLOOKUP function. It’s short for Horizontal LOOKUP, and "it searches for a value in the top row of a given table or an array of values, then returns a value in the same column from a row you specify in the table or array," according to Excel's documentation. The HLOOKUP function has the following syntax:
=HLOOKUP(input, range, selection_index_by_row)
Consider once again the teachers' database :
To find the D.O.B. for ID = 115, you would write the =HLOOKUP(115,B1:K5,5,0) function and get the output 03-Aug-88, as shown below:
LOOKUP Function
The LOOKUP function can be used "when you need to look in a single row or column and find a value in the same position in a second row or column," according to Excel's documentation.
The LOOKUP function has the following syntax:
=LOOKUP(input, range)
Consider the example below.
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| SR. NO. | ID | NAME | LAST NAME | DEPARTMENT | Formula | Output |
| 2 | 101 | Alexa | Das | CSE | =LOOKUP(103,B2:B6,E2:E6) | ? |
| 3 | 102 | Alisha | Loredo | EEE | =LOOKUP(107,B2:B6,E2:E6) | ? |
| 4 | 103 | Dev | Raghu | ECE | =LOOKUP(B3,B2:B6,C2:C6) | ? |
| 5 | 104 | Doug | Verma | IT | =LOOKUP("alisha",C2:C6,D2:D6) | ? |
| 6 | 105 | Jame | Will | EEE | =LOOKUP(105,B2:B6,D2:D6) | ? |
After applying the LOOKUP function, the result will be updated as shown below:
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| SR. NO. | ID | NAME | LAST NAME | DEPARTMENT | Formula | Output |
| 2 | 101 | Alexa | Das | CSE | =LOOKUP(103,B2:B6,E2:E6) | ECE |
| 3 | 102 | Alisha | Loredo | EEE | =LOOKUP(107,B2:B6,E2:E6) | EEE |
| 4 | 103 | Dev | Raghu | ECE | =LOOKUP(B3,B2:B6,C2:C6) | Alisha |
| 5 | 104 | Doug | Verma | IT | =LOOKUP("alisha",C2:C6,D2:D6) | Loredo |
| 6 | 105 | Jame | Will | EEE | =LOOKUP(105,B2:B6,D2:D6) | Will |
Conclusion
In this guide, you've learned various LOOKUP functions in MS Excel 2019. They can be used to automatically perform many operations and tasks that are otherwise time-consuming and prone to mistakes, such as working with arrays of data. Now you can explore by using these Excel features in your daily work.