Excel Workout 22 - IFERROR & VLOOKUP Functions!

[IlgarZarbaliyev]

Welcome to Excel Workout #22!
Difficulty Level:

This week’s challenge is designed to test your knowledge on IFERROR & INDEX Functions.

IFERROR Function
The IFERROR function is a built-in function in Microsoft Excel that helps handle errors in formulas and calculations. It allows you to specify the value or action to be taken if a formula results in an error.

VLOOKUP Function
The VLOOKUP function is a powerful lookup function in Microsoft Excel that allows you to search for a specific value in the leftmost column of a table and retrieve a corresponding value from a specified column.

Task

• To determine the Bonus (G Column) for each employee, it is necessary to multiply their salary by the department’s specific factor. These factors can be found within the range I2:J5. You can use the VLOOKUP Function to perform this calculation.

• In certain cells, you may come across errors such as “#N/A” and “#VALUE”. To handle these errors, you can utilize the IFERROR Function to replace them with the number 0.

Submission
Reply to this post with your formula code and solution file. Please be sure to blur or hide your formula code.

Period
This workout will be released on Thursday June 08, 2023, and the author’s solution will be posted on Thursday June 15, 2023.

Challenge 22.xlsx (8.3 MB)

Good luck,

Ilgar Zarbaliyev

[anonymous211]

[Challenge 22.xlsx|attachment](upload://fKOQfLqCvXZxGxioA3jI2PFN4We.xlsx) (8.3 MB)

[details="Summary"]
[spoiler]This text will be hidden[/spoiler]
[/details]

[IlgarZarbaliyev]

Will you post your reply once again? Thanks in advance.

[anonymous211]

Summary

`

Challenge 22.xlsx (8.3 MB)

`

[IlgarZarbaliyev]

@anonymous211 That is perfect now.

[mbraun]

[anonymous212]

[Challenge 22.xlsx|attachment](upload://opuwW3cslqRdiHjLa4ji28FMUyU.xlsx) (8.3 MB)

````=IFERROR(VLOOKUP(E2, I1:J6, 2, FALSE) * F2, 0)`

[anonymous53]

Challenge 22.xlsx (8.3 MB)

=IFERROR(F2*IFERROR(VLOOKUP(E2,I$2:J$6,2,FALSE),0),0)

[Keith]

Hi @IlgarZarbaliyev,

Here is my solution to this workout.

=IFERROR((VLOOKUP(E2,$I$2:$J$6,2,FALSE)*F2),0)

File attached:

Summary

Excel Workout Challenge #22 Keith.xlsx (8.3 MB)

Thanks for the workout.
Keith

[BasharatAsghar]

Bonus Formula:

=IFERROR($F2*VLOOKUP($E2,lookup_table,2,FALSE),0)

EDNA Excel Challenge 22 Solution Muhammad Basharat Asghar.xlsx (8.3 MB)

[FaridMammadzada]

=IFERROR(VLOOKUP(E2;$I$2:$J$6;2;0)*F2;0)

Challenge 22.xlsx (8.3 MB)

[Tural]

Formula:

=IFERROR(F2*(VLOOKUP(E2,$I$2:$J$6,2,FALSE)),0)

Challenge #22_Tural_Mammadov.xlsx (8.3 MB)

[IlgarZarbaliyev]

Thank you for participating in the Excel Challenge related to IFERROR & VLOOKUP Functions! I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to work with IFERROR & VLOOKUP Functions in Excel.

Here is my solution to Workout #22!

Solution

• Bonus for each employee:
  =VLOOKUP(E2,$I$2:$J$6,2,FALSE)*F2

• Replacing “#N/A” and “#VALUE” errors with number 0: =IFERROR(VLOOKUP(E2,$I$2:$J$6,2,FALSE)*F2, 0)

Challenge 22 with Solution.xlsx (8.3 MB)
Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

[Sujan_Chhetri]

Formula to calculate bonus: =IFERROR(VLOOKUP($E5,$I$2:$J$6,2,FALSE)*F5,0)

Snapshot of the Employee Table:

Here’s my workout file: Challenge 22.xlsx (8.3 MB)

[ak0333821799148]

=IFERROR(VLOOKUP(E2,$I$1:$J$6,2,FALSE)*F2,0)
[Challenge 22.xlsx|attachment](upload://qBMSjqc939P8dPdFn3RSVERppHD.xlsx) (8.3 MB)
[spoiler]
[details="Summary"]
This text will be blurred
[/details]
[/spoiler]