# Excel Workout 15 - Vlookup Function!

Welcome to Excel Workout #15!
Difficulty Level: This week’s challenge is designed to test your knowledge on Vlookup Function.

Vlookup Function
The VLOOKUP function in Excel is a powerful tool for searching and retrieving data from a table. VLOOKUP stands for “Vertical Lookup,” which means that it searches for a value in the first column of a table and returns a corresponding value from a specified column in the same row.

Goals

• Find the Product Code for every item based on the
information provided in table 3.

• Compute the Price Change, \$ for each
item by comparing the 2019 data from table 2 with the
previous year’s (2018) data from table 1.

• Compare the obtained values with the data in column I.

• Calculate the percentage Price Change in 2019
compared to the previous year (2018) by dividing the
2019 value by the 2018 value and subtracting 1.

• Compare the resulting values with the data in column J.

Submission

Period
This workout will be released on Monday May 15, 2023 , and the author’s solution will be posted on Sunday May 21, 2023 .
Challenge #15.xlsx (8.3 MB)

Good luck,

Ilgar Zarbaliyev

Price Changes, \$ = New Price - Old Price
=`IFNA(VLOOKUP([@[Product Code]],Price2022,2,0),0)-IFNA(VLOOKUP([@[Product Code]],Price2021,2,0),0)`

% Change = (New Price/Old Price)-1

`IFNA(VLOOKUP([@[Product Code]],Price2022,2,0),0)/IFNA(VLOOKUP([@[Product Code]],Price2021,2,0),0)-1`

*New price - Old price)/Old Price
OR

`=[@[Price Changes, \$]]/IFNA(VLOOKUP([@[Product Code]],Price2021,2,0),0)`

Where Price= New Price - Old Price

With XLOOKUP, There will be no need for IFNA or any other error handling functions as the “if not found” argument will deal with that.

1 Like
1 Like

It was a good workout looking forward to more lookup challenges…

=VLOOKUP([@Description],Data[#All],2,0)

=VLOOKUP([@[Product Code]],Price2022[#All],2,0)-VLOOKUP([@[Product Code]],Price2021[#All],2,0)

=VLOOKUP([@[Product Code]],Price2022[#All],2,0)/VLOOKUP([@[Product Code]],Price2021[#All],2,0)-1

1 Like

Here is my file
Challenge #15.xlsx (1.7 MB) 1 Like

Challenge #15_aamm.xlsx (8.3 MB)

Summary
``````#Price Changes, \$
=VLOOKUP([@[Product Code]];Price2022;2;FALSE)-VLOOKUP([@[Product Code]];Price2021;2;FALSE)

#Price Changes,%
=[@[Price Changes, \$]]/VLOOKUP([@[Product Code]];Price2021;2;FALSE)
``````
1 Like

Thanks for the workout. Here is my solution to the problem

Price Changes \$

=VLOOKUP([@[Product Code]],Price2022[#All],2,FALSE)-VLOOKUP([@[Product Code]],Price2021[#All],2,FALSE)

Price Changes %

=+[@[Price Changes, \$]]/VLOOKUP([@[Product Code]],Price2021[#All],2,0)

I taken the change column within the table. If the change is a positive number (increase from 2021) then the % must be a positive number not a negative percentage.

Thanks again
Keith Smith

1 Like

Price Change :
=VLOOKUP([@[Product Code]],Price2022,2,FALSE)-VLOOKUP([@[Product Code]],Price2021,2,FALSE)

Percentage Price Change :
=(VLOOKUP([@[Product Code]],Price2022,2,FALSE)/VLOOKUP([@[Product Code]],Price2021,2,FALSE))-1 Enterprise DNA Challenge #15 Solution Muhammad Basharat Asghar.xlsx (8.3 MB)

1 Like

Challenge #15 solution.xlsx (8.3 MB)

1 Like
``````
[details="Summary"]
[spoiler]

[/spoiler]
[/details]

``````
1 Like

Thank you for participating in the Excel Challenge related to Vlookup Function! 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 Vlookup Function in Excel.

Here is my solution to Workout #15:

• To find Product Code:
=VLOOKUP([@Description],Data9,2,FALSE)

• To find Price Changes, \$
=VLOOKUP([@[Product Code]],Price20228,2,0)-VLOOKUP([@[Product Code]],Price20212,2,0)

• To find Price Changes, %
=VLOOKUP([@[Product Code]],Price20228,2,0)/VLOOKUP([@[Product Code]],Price20212,2,0)-1

Challenge #15 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!

Challenge#15.xlsx (8.3 MB)

Product Code

``````=VLOOKUP([@Description];Data;2;0)
``````

Price Changes, \$

``````=VLOOKUP([@[Product Code]];Price2022[#All];2;0)-VLOOKUP([@[Product Code]];Price2021[#All];2;0)
``````

Price Changes, %

``````=VLOOKUP([@[Product Code]];Price2022[#All];2;0)/VLOOKUP([@[Product Code]];Price2021[#All];2;0)-1
``````
1 Like

Thank you, Farid, for your solutions.

I look forward to seeing your active participation in Excel Challenges.

Good luck.

1 Like

For Product Code: =VLOOKUP([@Description],Data,2,)
For Price Change: =VLOOKUP([@[Product Code]],Price2022,2,)-VLOOKUP([@[Product Code]],Price2021,2,)

For Price Change %: =VLOOKUP([@[Product Code]],Price2022,2,)/VLOOKUP([@[Product Code]],Price2021,2,

Challenge #15 Shan.xlsx (8.3 MB)

1 Like

Formula to calculate Product code: =VLOOKUP([@Description],Data,2,FALSE)

Formula to calculate Price change from year 2021 to 2022: =VLOOKUP([@Description],Data,2,FALSE)

Formula to calculate %change in price of a product: =IFERROR([@[Price Changes, \$]]/VLOOKUP([@[Product Code]],Price2021,2,FALSE),“Error”)

My workout file: Challenge #15.xlsx (8.3 MB)

1 Like

Challenge #15.xlsx (8.3 MB)

``````[spoiler]
Product Code = VLOOKUP(E3,\$B\$27:\$C\$35,2,0)
Price Changes, \$ = VLOOKUP(F3,\$B\$15:\$C\$23,2,0)-VLOOKUP(F3,\$B\$3:\$C\$11,2,0)
Price Changes, % = VLOOKUP(F3,\$B\$15:\$C\$23,2,0)/VLOOKUP(F3,\$B\$3:\$C\$11,2,0)-1
[/spoiler]
``````
Summary

Product Code =VLOOKUP([@Description],Data[#All],2,FALSE)
Price in 2022 =XLOOKUP([@[Product Code]], Price2022[Product Code],Price2022[Price per piece in 2022, \$])
Price in 2021 =XLOOKUP([@[Product Code]],Price2021[Product Code],Price2021[Price per piece in 2021, \$])
Price Change =D27-E27
Price Change %
Challenge #15.xlsx (8.3 MB)
=(D27/E27)-1

1 Like