How to Find a Matching Number in Another Worksheet Using Excel Formulas

In this article, we will learn how to use Excel formulas to find a matching number in another worksheet. This is a useful skill when you have data in multiple worksheets and you want to quickly locate a specific value. For example, you may have a list of products in one worksheet and their prices in another worksheet, and you want to find the price of a certain product.

There are different ways to achieve this task, but we will focus on three main methods:

• Using the VLOOKUP function
• Using the INDEX and MATCH functions
• Using the HYPERLINK function

We will explain the basic theory behind each method, the steps to follow, and the advantages and disadvantages of each method. We will also provide a scenario with real data to illustrate each method with an example.

Method 1: Using the VLOOKUP Function

The VLOOKUP function is one of the most popular and widely used functions in Excel. It allows you to look up a value in the leftmost column of a table and return the corresponding value from another column in the same table. You can use this function to find a matching number in another worksheet by specifying the worksheet name and the range of cells that contain the table.

Basic Theory

The syntax of the VLOOKUP function is:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Where:

• lookup_value is the value that you want to find in the leftmost column of the table.
• table_array is the range of cells that contains the table. You can use the worksheet name followed by an exclamation mark (!) to refer to another worksheet, such as Sheet2!A1:B10.
• col_index_num is the column number in the table from which you want to return the value. The leftmost column is 1, the second column is 2, and so on.
• [range_lookup] is an optional argument that specifies whether you want an exact match or an approximate match. You can use TRUE or 1 for an approximate match, or FALSE or 0 for an exact match. If you omit this argument, the default value is TRUE.

Steps

To use the VLOOKUP function to find a matching number in another worksheet, follow these steps:

1. In the worksheet where you want to display the result, select a cell and type =VLOOKUP (.
2. Type the lookup_value or select the cell that contains the lookup_value.
3. Type a comma (,) and then click on the worksheet that contains the table.
4. Select the range of cells that contains the table and press the F4 key to lock the reference.
5. Type a comma (,) and then type the col_index_num or select the cell that contains the col_index_num.
6. Type a comma (,) and then type FALSE or 0 for an exact match.
7. Type a closing parenthesis ()) and press the Enter key.

Example

Suppose you have the following data in two worksheets:

Sheet1:

Sheet2:

You want to find the price of the product with ID 103 in Sheet1. To do this, you can use the following formula in Sheet1:

=VLOOKUP (B3,Sheet2!A2:B6,2,FALSE)

This formula will return 2.0, which is the price of the product with ID 103 in Sheet2.

Advantages and Disadvantages

The VLOOKUP function is easy to use and understand, and it can handle both text and numeric values. However, it also has some limitations, such as:

• It can only look up values in the leftmost column of the table. If you want to look up values in other columns, you need to rearrange the table or use another method.
• It can only return values from one column in the table. If you want to return values from multiple columns, you need to use multiple VLOOKUP functions or another method.
• It can be slow and inefficient if you have a large table or multiple worksheets, as it needs to scan the entire table for each lookup.

Method 2: Using the INDEX and MATCH Functions

The INDEX and MATCH functions are another common way to find a matching number in another worksheet. The INDEX function returns a value or the reference to a value from within a table or range, and the MATCH function returns the relative position of a value within a range. You can combine these two functions to look up a value in any column of a table and return the corresponding value from another column in the same table.

Basic Theory

The syntax of the INDEX function is:

=INDEX (array, row_num, [column_num])

Where:

• array is the range of cells that contains the table or the values that you want to return.
• row_num is the row number in the array from which you want to return the value. You can use the MATCH function to find the row_num based on the lookup_value.
• [column_num] is an optional argument that specifies the column number in the array from which you want to return the value. If you omit this argument, the default value is 1.

The syntax of the MATCH function is:

=MATCH (lookup_value, lookup_array, [match_type])

Where:

• lookup_value is the value that you want to find in the lookup_array.
• lookup_array is the range of cells that contains the values that you want to match with the lookup_value.
• [match_type] is an optional argument that specifies how you want to match the lookup_value with the lookup_array. You can use 1 for a less than or equal to match, 0 for an exact match, or -1 for a greater than or equal to match. If you omit this argument, the default value is 1.

Steps

To use the INDEX and MATCH functions to find a matching number in another worksheet, follow these steps:

1. In the worksheet where you want to display the result, select a cell and type =INDEX (.
2. Click on the worksheet that contains the table and select the range of cells that contains the values that you want to return. Press the F4 key to lock the reference.
3. Type a comma (,) and then type MATCH (.
4. Type the lookup_value or select the cell that contains the lookup_value.
5. Type a comma (,) and then click on the worksheet that contains the table and select the range of cells that contains the values that you want to match with the lookup_value. Press the F4 key to lock the reference.
6. Type a comma (,) and then type 0 for an exact match.
7. Type a closing parenthesis ()) twice and press the Enter key.

Example

Using the same data as before, suppose you want to find the product name of the product with price 1.2 in Sheet2. To do this, you can use the following formula in Sheet2:

=INDEX (Sheet1!B2:B6,MATCH (B3,Sheet1!A2:A6,0))

This formula will return Date, which is the product name of the product with price 1.2 in Sheet1.

Advantages and Disadvantages

The INDEX and MATCH functions are more flexible and powerful than the VLOOKUP function, as they can:

• Look up values in any column of the table, not just the leftmost column.
• Return values from any column of the table, not just one column.
• Handle both horizontal and vertical lookups, as well as two-dimensional lookups.

However, they also have some drawbacks, such as:

• They are more complex and harder to understand than the VLOOKUP function, especially for beginners.
• They require more typing and more arguments than the VLOOKUP function, which can increase the chances of errors and typos.
• They can be slow and inefficient if you have a large table or multiple worksheets, as they need to scan the entire table for each lookup.

Method 3: Using the HYPERLINK Function

The HYPERLINK function is another way to find a matching number in another worksheet. It allows you to create a clickable link that takes you to a specific cell or location in another worksheet or workbook. You can use this function to find a matching number in another worksheet and then jump to the cell that contains the matching number.

Basic Theory

The syntax of the HYPERLINK function is:

=HYPERLINK (link_location, [friendly_name])

Where:

• link_location is the cell or location that you want to link to. You can use the worksheet name followed by an exclamation mark (!) and the cell reference, such as Sheet2!A2.
• [friendly_name] is an optional argument that specifies the text that you want to display as the link. If you omit this argument, the default value is the link_location.

Steps

To use the HYPERLINK function to find a matching number in another worksheet, follow these steps:

1. In the worksheet where you want to display the result, select a cell and type =HYPERLINK (.
2. Type the link_location or select the cell that contains the matching number. Press the F4 key to lock the reference. 3. Type a comma (,) and then type the friendly_name or select the cell that contains the friendly_name. 4. Type a closing parenthesis ()) and press the Enter key.

Example

Using the same data as before, suppose you want to find the product ID of the product with name Cherry in Sheet1. To do this, you can use the following formula in Sheet1:

=HYPERLINK (Sheet2!A4,B4)

This formula will display Cherry as a link in the cell. When you click on the link, it will take you to the cell A4 in Sheet2, which contains the product ID 103.

Advantages and Disadvantages

The HYPERLINK function is a convenient and interactive way to find a matching number in another worksheet, as it can:

• Create a direct link to the cell that contains the matching number, which saves you time and effort to navigate between worksheets.
• Display a custom text as the link, which can make the result more user-friendly and meaningful.

However, it also has some limitations, such as:

• It can only return the reference to the cell that contains the matching number, not the value itself. If you want to display the value, you need to use another function or method.
• It can be affected by changes in the worksheet name or the cell reference, which can break the link or make it inaccurate.