Introduction

25

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

We’ll take these one by one.

The `ADDRESS`

function gives us the address for a cell based on the given row and column numbers.

The `ADDRESS`

function has the following syntax:

`1`

`=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])`

The arguments used in this function include:

`row_num`

=**Required**, a numeric value that specifies the row number to be used in the cell reference.`column_num`

=**Required**, a numeric value that specifies the column number to use in the cell reference.`abs_num`

=**Optional**, a numeric value that specifies the type of reference to return as mentioned in the table below.

A | B |
---|---|

abs_num | Returns type of reference |

1 or omitted | Absolute |

2 | Absolute row; relative column |

3 | Relative row; absolute column |

4 | Relative |

`A1`

=**Optional**, a logical value that specifies the A1 or R1C1 reference style. A1 style means columns are labeled alphabetically and rows are labeled numerically, whereas R1C1 reference style means both columns and rows are labeled numerically. If the`A1`

argument value is TRUE or omitted in the function, the`ADDRESS`

function will return an A1-style reference; if the`A1`

argument value is FALSE, the`ADDRESS`

function will return an R1C1-style reference.`sheet_text`

=**Optional**, a text value that specifies the name of the worksheet to be used as the external reference. If the`sheet_text`

argument value is omitted, no sheet name will be used, and the address returned by the function will refer to a cell on the current sheet.

Let us consider a scenario in which you can implement the `ADDRESS`

function. Consider the example 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) | ? | |||

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) | ? |

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!R7C11 |

5 | 18 | 12 | 4 | 1 | =ADDRESS(B5, C5, D5, E5) | L18 |

The `AREAS`

function gives the number of areas in a given reference where an area is a range of contiguous cells or a single cell.

It has the following syntax:

`1`

`=AREAS(reference)`

The argument used in this function is:

`reference`

=**Required**, a reference to a cell or range of cells that refers to multiple areas. If you want to specify a single argument for multiple references, then you must include extra sets of parentheses. In that way the comma will not be interpreted as a field separator by Excel.

Let’s consider a scenario in which you can implement the `AREAS`

function. Consider the example 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) | ? | |||

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) | ? |

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 |

The `UNIQUE`

function returns unique values from a list of values.

It has the following syntax:

`1`

`=UNIQUE(range)`

Consider the scenario 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 |

The `CHOOSE`

function uses index_num to return a value from the list of value arguments.

It has the following syntax:

`1`

`=CHOOSE(index_num, value1, [value2], ...)`

The arguments used in this function include:

`index_num`

=**Required**, specifies the selected value argument. This argument must be a number that lies between 1 and 254 or a formula or reference to a cell containing a number between 1 and 254.`Value1, value2, ...`

=`Value1`

is a**Required**argument, whereas subsequent values are**Optional**arguments.

Let’s consider an example.

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) | ? |

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 |

The `COLUMN`

function returns the column number of the given cell reference.
It has the following syntax:

`1`

`=COLUMN([reference])`

`reference`

argument used in the function is **Optional**. If the argument is omitted in the function, then the output will be the column number in which the formula appears. For an example, `=COLUMN(B10)`

returns `2`

as output because column B is the second column.

The `COLUMNS`

function returns the number of columns in the given array or reference.
It has the following syntax:

`1`

`=COLUMNS(array)`

`array`

argument used in the function is **Required**. This argument is an array or array formula, or a reference to a range of cells for which you want the number of columns. For example, `=COLUMNS(A1:E1)`

returns `5`

as output.

The `INDEX`

function uses an index to choose a value from a reference or array.
It has the following syntax:

`1`

`=INDEX(array, row_num, [column_num])`

The arguments used in this function include:

`array`

=**Required**, a range of cells or an array constant.`row_num`

=**Required**, selects the row in an array from which to return a value. In case the`row_num`

is omitted,`column_num`

is**Required**.`column_num`

=**Optional**, selects the column in array from which to return a value. In case`column_num`

is omitted,`row_num`

is**Required**.

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) | ? |

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 |

The `MATCH`

function searches a specific item in a range of cells, and returns the relative position of that item in the given range.
It has the following syntax:

`1`

`=MATCH(lookup_value, lookup_array, [match_type])`

The arguments used in this function include:

`lookup_value`

=**Required**, the value that you want to match in the given`lookup_array`

. It can be a number, text, logical value, a cell reference to a number.`lookup_array`

=**Required**, the range of cells which is being searched.`match_type`

=**Optional**, specifies how the Excel matches`lookup_value`

with values in the given`lookup_array`

. The default value is 1 for this argument. The table below describes on what basis the function finds values based on the setting of the`match_type`

argument:

Match_type | Behavior |
---|---|

1 or omitted | It finds the largest value that is less than or equal to `lookup_value` . The values in the `lookup_array` argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. |

0 | It finds the first value that is exactly equal to `lookup_value` . The values in the `lookup_array` argument can be in any order |

-1 | It finds the smallest value that is greater than or equal to `lookup_value` . The values in the `lookup_array` argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on. |

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) | ? |

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 |

You can use `VLOOKUP`

function when you need to find values in a table or a range by row.
The `VLOOKUP`

function has the following syntax:

`1`

`=VLOOKUP(lookup value, table_array, col_index_num, [range_lookup])`

The arguments used in this function include:

`lookup value`

=**Required**, the value that you want to look up.`table_array`

=**Required**, the range where the`lookup value`

is located.`lookup value`

should always be in the first column in the`table_array`

for`VLOOKUP`

function to work correctly.`col_index_num`

=**Required**, the column number in the`table_array`

that contains the return value of the function.`range_lookup`

=**Optional**, its value is`TRUE`

if you want an approximate match of the return value or`FALSE`

if you want an exact match. The default value for this argument is`TRUE`

.

Let’s 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 wanted 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:

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.
The `HLOOKUP`

function has the following syntax:

`1`

`=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`

Consider once again the database of information on a group of teachers.:

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:

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.
The `LOOKUP`

function has the following syntax:

`1`

`=LOOKUP(lookup_value, lookup_vector, [result_vector])`

The arguments used in this function include:

`lookup_value`

=**Required**, a value that`LOOKUP`

function searches for in the first vector. It can be a number, text, logical value, a cell reference to a number.`lookup_vector`

=**Required**, a range that contains only one row or one column. It can be text, numbers, or logical values and must be placed in ascending order.`result_vector`

=**Optional**, a range that contains only one row or column. The`result_vector`

argument must be the same size as`lookup_vector`

.

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) | ? |

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 |

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.

25