How to Use HLOOKUP with IF Condition in Excel (3 Examples)

The HLOOKUP function in Excel looks up a value in the top row of a horizontal table and returns a matching value in a specific row below. But in real-time tasks, you may not want the lookup to happen all the time. You might only want it when a condition is true.

By combining HLOOKUP with the IF function, you can add logic to your formula. This allows you to control when the lookup should run and what to display if the condition is not met.

For example, you can return a bonus amount based on a sales level only if the total sales are above 3000. If the sales are too low, the result can be something like No Bonus instead.

In this article, we’ll show how to use HLOOKUP with the IF function using simple methods. We’ll return bonus amounts based on sales levels, but only if the sales are high enough.

In the following dataset, we have a sales performance report that includes employee names, their total sales, and assigned performance levels. Column A lists the Names, Column B shows the Sales amount, and Column C contains the Level assigned to each employee based on performance. Column D is currently empty and will be used to display the Bonus.

We also have a lookup table that shows the bonus amount for each level. However, we only want to assign a bonus if the salesperson has sold more than 3000.

We’ll use this dataset to build a formula that checks each person’s sales, and if the amount is greater than 3000, returns the correct bonus using HLOOKUP.

This method combines the HLOOKUP and IF functions in Excel to check if the sales amount meets a minimum requirement and then returns the corresponding bonus based on the employee’s performance level.

We’ll set a condition so that the bonus is only applied when the sales are greater than 3000. If not, the result will show No Bonus.

Here’s a step-by-step instruction how to apply this method:

➤ Open your dataset in Excel.
➤ Click on cell D2, where we want the bonus result to appear.
➤ Enter the following formula:

➤ Press Enter.
➤ The result will show the correct bonus amount if the sales in cell B2 is above 3000. Otherwise, it will display No Bonus.

➤ Now, drag the fill handle down from D2 to D11 to apply the formula to the rest of the rows.

In some cases, the Level value in your dataset might not match any entry in the lookup table. This can happen due to typos, missing values, or unexpected data. If that happens, HLOOKUP returns a #N/A error, which looks messy in a report.

To prevent this, we can use the IFERROR function to catch errors and return a clean message like Invalid Level instead.

Here’s a step-by-step instructions to do that:

➤ Click on cell D2, where the bonus should be displayed.
➤ Type the following formula:

➤ Press Enter.
➤ This formula will return the correct bonus if the sales are greater than 3000, and the Level value matches one in the lookup table. If the Level is not found, the formula shows Invalid Level instead of an error.

➤ Drag the fill handle down from D2 to D11 to copy the formula to the rest of the rows.

In some situations, you may want to apply custom rules beyond just checking sales. For example, you might want to assign a higher bonus to Platinum level only if sales are above 8000, give Gold a bonus regardless of sales, and exclude Bronze completely.

This can be done by using nested IF statements along with HLOOKUP function to apply different logic for each level.

Here’s how to apply this method:

➤ Click on cell D2, where the result will be shown.
➤ Enter the following formula:

➤ Press Enter.
➤ This formula applies custom logic based on both Level and Sales.

➤ Drag the fill handle down from D2 to D11 to apply the formula to all rows.

Frequently Asked Questions

What is the purpose of using IF with HLOOKUP in Excel?

Using IF with HLOOKUP helps you control when the lookup takes place. Instead of always showing a result, the formula checks a condition first. If the condition is true, it returns a value. If not, it can show a message like “No Bonus.”

What happens if the lookup value is not found in the table?

If the level is entered incorrectly or doesn’t exist in the table, HLOOKUP will return a #N/A error. You can prevent this by using IFERROR to catch the error and display a clear message.
Example:
=IFERROR(HLOOKUP(C2, G:J, 2, FALSE), “Invalid Level”)

What is the difference between HLOOKUP and VLOOKUP?

HLOOKUP checks across the top row and pulls a value in a lower row. VLOOKUP checks the first column and pulls a value in a column to the right.
Use HLOOKUP when your table has values organized across rows instead of columns.

Wrapping Up

If you’re managing bonus payouts, sales reports, or performance tracking, using HLOOKUP with IF gives you a clear way to apply rules without making your sheet overly complex. It helps you decide what result to show based on conditions like sales amount or level accuracy.

We worked through real examples where the bonus is only given when certain rules are met. Along the way, we also handled errors and added custom logic for specific sales levels.

This method is easy to adapt for many other use cases such as grading, discounts, or role-based permissions. Once you understand how to link HLOOKUP with IF, you’ll be able to build more responsive and reliable Excel formulas.