Excel Workout 15 - Vlookup Function!

[IlgarZarbaliyev]

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
Please follow the directions given below, which include downloading the Excel worksheet required to perform the challenge tasks. Once you have completed the download, proceed to take the challenge and test your skills.

Task

• 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
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 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

[QuadriAtharuOlayinka]

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.

Quadri Challenge #15.xlsx (8.3 MB)

[mbraun]

[Anonymous14]

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

Challenge #15 Golam Mohammad Sardar.xlsx (8.3 MB)
`

[ClaudiaRoxo]

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

[AAMM]

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)

[Keith]

Hi @IlgarZarbaliyev,

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.

Excel Workout Challenge #15 Keith Smith.xlsx (8.3 MB)

Thanks again
Keith Smith

[BasharatAsghar]

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)

[KieftyKids]

Challenge #15 solution.xlsx (8.3 MB)

[munachimarcel]

[details="Summary"]
[spoiler]
[Munachi_Challenge #15.xlsx|attachment](upload://a0RJwwNsxhsrvqSzFRulhzi68cp.xlsx) (8.3 MB)

[/spoiler]
[/details]

[IlgarZarbaliyev]

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!

[FaridMammadzada]

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

[IlgarZarbaliyev]

Thank you, Farid, for your solutions.

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

Good luck.

[Shan]

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)

[Sujan_Chhetri]

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)

[m.shaygani1994]

Answer:

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]

[chidinmaamara.eke73]

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