IF + VLOOKUP Nested Function in Excel

Nested functions in Excel refer to using one function inside another function. This allows you to perform more complex calculations by combining multiple functions.

Example: IF + VLOOKUP Nested Function

Let’s say you have a sales data table with the following columns:

Salesperson	Sales Amount
John	1200
Alice	2000
Bob	800
Charlie	1500

You want to check if a salesperson’s sales are greater than 1000 and if so, return a “Bonus” (e.g., 10% of the sales). Otherwise, return “No Bonus”. You will use a combination of the IF function and VLOOKUP function to achieve this.

Formula:

=IF(VLOOKUP("John", A2:B5, 2, FALSE) > 1000, VLOOKUP("John", A2:B5, 2, FALSE) * 0.1, "No Bonus")

Explanation:

• VLOOKUP("John", A2:B5, 2, FALSE): This looks up the value of “John” in the first column (Salesperson) and retrieves the corresponding sales amount from the second column. It will return 1200 for John.
• IF(... > 1000, ... * 0.1, "No Bonus"): After getting the sales amount (1200 in this case), the IF function checks if it is greater than 1000. If true, it calculates 10% of the sales (1200 * 0.1 = 120). If false, it returns “No Bonus”.

Result:

For “John,” the formula returns 120 (10% of 1200) because the sales are greater than 1000.

You can replace “John” with other salespeople to check their bonuses or use cell references for a more dynamic approach.

Use Case:

Nested functions like this are very useful for complex data analysis, such as calculating commissions, applying conditional formatting, or analyzing data based on multiple criteria.