How to Use HLOOKUP with Two Lookup Values in Excel [3 Examples]

To use HLOOKUP with two lookup values in Excel, follow the steps below:

1. Select cell B1.
2. Write this formula:=B6&B7
3. Press the ENTER button.
4. From cell B1 through D1, drag the Fill Handle.
5. Again, select a cell B8.
6. Copy this formula: =HLOOKUP(B6 & B7, A1:C4, 4, FALSE)
7. To view the outcome, press ENTER.

This formula searches for the concatenated value of B6 and B7 in the first row of the table A1:C4, and if it finds a match, it returns the corresponding value from the 4th row of the table.

This is useful when you need to perform a lookup based on a combination of two values.

Example 1: Concatenate with Ampersand and HLOOKUP for Two Lookup Values in Excel

You can use the Concatenate with Ampersand and HLOOKUP functions to develop a formula that will extract data from a table depending on a combination of two criteria. By using the & operator to combine two criteria into one, you may quickly search for a specific value in a database. Then, you can use HLOOKUP to locate the relevant value.

Syntax

=HLOOKUP(Lookup_value,table_array,row_index_number,[range_lookup])

Formula

=HLOOKUP(B6 & B7, A1:C4, 4, FALSE)

Formula Breakdown

This Excel formula uses the HLOOKUP function to find a specific value in the fourth row of a given range (A1:C4) by combining the contents of cells B6 and B7 using the & operator as a single concatenated criterion.

The formula searches for this concatenated value in the first row of the specified range and returns the corresponding value from the fourth row when an exact match is found, providing a straightforward method for retrieving data from a table based on the combination of the two criteria.

Follow these steps below to HLOOKUP for two lookup values:

1. Select cell B1.
2. Write this formula:=B6&B7
3. Press the ENTER button.
4. From cell B1 through D1, drag the Fill Handle.
5. 
6. Again, select a cell B8.
7. Copy this formula: =HLOOKUP(B6 & B7, A1:C4, 4, FALSE)
8. To view the outcome, press ENTER.

Final Result

Finally, the desired output has been successfully located.

Example 2: Combining CONCATENATE with HLOOKUP for Two Lookup Values in Excel

Another approach is to create a helper column using CONCATENATE, followed by a horizontal lookup using the HLOOKUP function.

Syntax

=HLOOKUP(CONCATENATE([text_1],[text_2],..),table_array,row_index_number,[range_lookup])

Formula

=HLOOKUP(CONCATENATE(B6," ",B7),A1:D4,4,FALSE)

Formula Breakdown

This formula combines the CONCATENATE function with HLOOKUP in Excel to find a specific value. It first concatenates the contents of cells B6 and B7 with a space in between, creating a single search value.

The HLOOKUP function then searches for this concatenated value within the range A1 to D4 and retrieves the corresponding value from the fourth row of that range. The “FALSE” parameter indicates an exact match, ensuring that the search is case-sensitive and returns an exact match.

Combining CONCATENATE with HLOOKUP for Two Lookup Values, you need to follow these steps:

1. Choose cell B1.
2. Copy this formula:=CONCATENATE(B2,” “,B3) to create a Helper row
3. Press ENTER.
4. Drag the Fill Handle from cell B1 to D1.
5. 
6. Again, Select cell B8.
7. Type this formula: =HLOOKUP(CONCATENATE(B6,” “,B7),A1:D4,4,FALSE)
8. Hit ENTER to see the result.

Final Result

At last, I’ve achieved accurate results by employing two lookup values to find an exact match.

Example 3: Combining TEXTJOIN with HLOOKUP for Two Lookup Values in Excel

You can use the combination of TEXTJOIN and HLOOKUP functions for two lookup values and return one value. The TEXTJOIN function is applied in the helper column and the HLOOKUP function searches the value with the help of the helper column.

Syntax

=HLOOKUP(TEXTJOIN (delimiter, ignore_empty, text1, [text2], ..),table_array,row_index_number,[range_lookup])

Formula

=HLOOKUP(TEXTJOIN(" ", TRUE, B6, B7), A1:D4, 4, FALSE)

Formula Breakdown

This Excel formula uses the HLOOKUP function to search for a specific value in the fourth row of a given range (A1:D4) based on the result of concatenating the contents of cells B6 and B7 with space as a delimiter using the TEXTJOIN function.

The TEXTJOIN function creates a single text string by joining the values of B6 and B7 with a space in between. The HLOOKUP function then looks for this concatenated value in the first row of the specified range and returns the corresponding value from the fourth row when an exact match is found.

To look up two values using TEXTJOIN and HLOOKUP functions, follow these steps:

1. Pick cell B1.
2. Follow this formula:=TEXTJOIN(” “, TRUE, B2, B3) to create a Helper row
3. Press ENTER.
4. From cell B1 through D1, drag the Fill Handle.
5. 
6. Again, choose cell B8.
7. Type this formula: =HLOOKUP(CONCATENATE(B6,” “,B7),A1:D4,4,FALSE)
8. To view the outcome, press ENTER.

Final Result

This formula is useful when you want to retrieve data from a table based on a combination of two criteria.

Conclusion

Using the three formulas demonstrated in these examples, you can perform a horizontal lookup with multiple values and retrieve a single result.

Frequently Asked Questions

Can Hlookup return multiple values?

No, the HLOOKUP function in Excel is designed to return a single value. It searches for a specified value in the first row of a table and returns the corresponding value from a specified row below. If multiple matches are found in the first row, HLOOKUP will return the value associated with the first match it encounters.

Can you use both Hlookup and VLOOKUP together?

Yes, you can use both HLOOKUP and VLOOKUP functions together in Excel. HLOOKUP is for horizontal lookups, where you search for a value in the top row and retrieve a corresponding value from a row below.

VLOOKUP is for vertical lookups, where you search for a value in the leftmost column and retrieve a value from a column to the right. Using them together can be helpful when you need to perform both vertical and horizontal searches in a table to extract specific data points.

How do I do a Hlookup in Excel for multiple columns?

To perform a HLOOKUP across multiple columns in Excel, follow these steps:

1. Select the cell where you want the result.
2. Type the formula: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
3. Replace lookup_value with the value to search for.
4. Specify the data range in table_array.
5. Set row_index_num to the row number in the table containing the data you want to retrieve.
6. For range_lookup, use FALSE for an exact match or TRUE for an approximate match.
7. Press Enter to get the result.

Example: =HLOOKUP(“John”, A1:D10, 3, FALSE).

This formula searches for “John” in the third row of the specified range A1:D10 and returns the corresponding value.