How to Perform Complex Lookups in Excel and Display the Result in Another Cell

Sometimes, we may need to look up a value from a table or a range of cells and return it to another cell. For example, we may have a list of products and their prices, and we want to find the price of a specific product and display it in a different cell. There are different ways to achieve this task in Excel, depending on the structure and layout of the data. Some of the most common methods are:

• Using the VLOOKUP function
• Using the INDEX and MATCH functions
• Using the SEARCH and ISNUMBER functions
• Using the Paste Special option
• Using a cell reference

Procedures

Using the VLOOKUP Function

The VLOOKUP function is one of the most popular lookup functions in Excel. It allows us to search for a value in the first column of a table or a range and return a value from another column in the same row. The syntax of the VLOOKUP function is:

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

where:

• lookup_value is the value we want to find
• table_array is the table or range of cells where we want to search
• col_index_num is the column number in the table or range that contains the value we want to return
• range_lookup is an optional argument that specifies whether we want an exact match (FALSE) or an approximate match (TRUE or omitted)

To use the VLOOKUP function, we need to follow these steps:

1. Identify the value we want to look up and the table or range where we want to search
2. Identify the column number in the table or range that contains the value we want to return
3. Enter the VLOOKUP formula in the cell where we want to display the result
4. Adjust the range_lookup argument if needed

Using the INDEX and MATCH Functions

The INDEX and MATCH functions are two powerful functions that can work together to perform complex lookups. The INDEX function returns a value from a specific position in a table or a range, while the MATCH function returns the relative position of a value in a row or a column. The syntax of the INDEX function is:

=INDEX(array, row_num, [column_num])

where:

• array is the table or range of cells where we want to return a value
• row_num is the row number in the table or range that contains the value we want to return
• column_num is an optional argument that specifies the column number in the table or range that contains the value we want to return

The syntax of the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])

where:

• lookup_value is the value we want to find
• lookup_array is the row or column where we want to search
• match_type is an optional argument that specifies whether we want an exact match (0), a less than match (-1), or a greater than match (1)

To use the INDEX and MATCH functions, we need to follow these steps:

1. Identify the value we want to look up and the table or range where we want to search
2. Identify the row or column where we want to search for the lookup value
3. Identify the row or column where we want to return a value
4. Enter the INDEX formula in the cell where we want to display the result, and use the MATCH function as the row_num or column_num argument

Using the SEARCH and ISNUMBER Functions

The SEARCH and ISNUMBER functions are two text functions that can help us to find a specific text value in a cell and return a value from another cell. The SEARCH function returns the position of a text value within another text value, while the ISNUMBER function returns TRUE if a value is a number and FALSE otherwise. The syntax of the SEARCH function is:

=SEARCH(find_text, within_text, [start_num])

where:

• find_text is the text value we want to find
• within_text is the text value where we want to search
• start_num is an optional argument that specifies the position where we want to start the search

The syntax of the ISNUMBER function is:

=ISNUMBER(value)

where:

• value is the value we want to test

To use the SEARCH and ISNUMBER functions, we need to follow these steps:

1. Identify the text value we want to find and the cell where we want to search
2. Identify the cell where we want to return a value
3. Enter the ISNUMBER formula in the cell where we want to display the result, and use the SEARCH function as the value argument

Using the Paste Special Option

The Paste Special option is a handy feature that allows us to copy and paste values, formulas, formats, and other options from one cell to another. To use the Paste Special option, we need to follow these steps:

1. Identify the cell that contains the value we want to copy
2. Copy the cell using Ctrl+C or the Copy command
3. Select the cell where we want to paste the value and right-click on it
4. Choose Paste Special from the dropdown menu
5. In the Paste Special dialog box, select Values and click OK

Using a Cell Reference

A cell reference is a simple way to refer to a cell or a range of cells in a formula. To use a cell reference, we need to follow these steps:

1. Identify the cell that contains the value we want to return
2. Click the cell where we want to enter a reference to another cell
3. Type an equals (=) sign in the cell
4. Click the cell in the same worksheet we want to make a reference to, and the cell name is automatically entered after the equal sign
5. Press Enter to create the cell reference

Comprehensive Explanation

To illustrate the different methods of searching for a value and putting it in another cell in Excel, we will use the following sample data set that contains the product name, size, and price of some items.

Suppose we want to find the price of a medium-sized shirt and display it in cell F2. We can use any of the methods described above to achieve this task.

Using the VLOOKUP Function

To use the VLOOKUP function, we need to enter the following formula in cell F2:

=VLOOKUP("Shirt",A2:C10,3,FALSE)

This formula will search for the value “Shirt” in the first column of the range A2:C10, and return the value from the third column in the same row. The FALSE argument indicates that we want an exact match. The result is 12, which is the price of a medium-sized shirt.

Using the INDEX and MATCH Functions

To use the INDEX and MATCH functions, we need to enter the following formula in cell F2:

=INDEX(C2:C10,MATCH("Shirt",A2:A10,0))

This formula will use the MATCH function to find the position of the value “Shirt” in the range A2:A10, and return the value from the same position in the range C2:C10 using the INDEX function. The 0 argument indicates that we want an exact match. The result is 12, which is the same as the previous method.

Using the SEARCH and ISNUMBER Functions

To use the SEARCH and ISNUMBER functions, we need to enter the following formula in cell F2:

=IF(ISNUMBER(SEARCH("Shirt",A2)),C2,"")

This formula will use the SEARCH function to find the position of the value “Shirt” in cell A2, and use the ISNUMBER function to check if the result is a number. If it is, the formula will return the value from cell C2, otherwise it will return a blank cell. The result is 10, which is the price of a small-sized shirt. To get the price of a medium-sized shirt, we need to drag the formula down to cell F3, where the result is 12.

Using the Paste Special Option

To use the Paste Special option, we need to follow these steps:

1. Select cell C3 that contains the value we want to copy
2. Copy the cell using Ctrl+C or the Copy command
3. Select cell F2 where we want to paste the value and right-click on it
4. Choose Paste Special from the dropdown menu
5. In the Paste Special dialog box, select Values and click OK

The result is 12, which is the same as the previous methods.

Using a Cell Reference

To use a cell reference, we need to follow these steps:

1. Click cell F2 where we want to enter a reference to another cell
2. Type an equals (=) sign in the cell
3. Click cell C3 in the same worksheet we want to make a reference to, and the cell name is automatically entered after the equal sign
4. Press Enter to create the cell reference

The result is 12, which is the same as the previous methods.