Excel Workout 19 - Hlookup & Match Functions!

Workouts Excel
1

Welcome to Excel Workout #19!
Difficulty Level:
image

This week’s challenge is designed to test your knowledge on Hlookup & Match Functions.

image
image1254×598 66.7 KB

sjoQ3ZNpylNRUec4DiNa)

HLOOKUP Function
The HLOOKUP function in Excel is a powerful tool used to search for a value in the top row of a table or range of cells and return a corresponding value from a specified row. The “H” in HLOOKUP stands for “Horizontal,” indicating that the function searches horizontally across rows.

MATCH Function
The MATCH function in Excel is used to find the position of a specified value within a range of cells.

Task

  • Retrieve the data from the initial table in the worksheet,
    located in cells A1:A11. This extraction should be based
    on the chosen state names in cells B14 and C14, as well
    as the months listed in the range A15:A19.

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

image
image735×266 25.6 KB

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

Good luck,

Ilgar Zarbaliyev

1 Like
2

For Texas:
=HLOOKUP($A15, $A$1:$M$11, MATCH(B$14, $A$1:$A$11, 0), FALSE)

For New Mexico
=HLOOKUP($A15, $A$1:$M$11, MATCH(C$14, $A$1:$A$11, 0), FALSE)

Challenge_#19_Tural_Mammadov
Challenge_#19_Tural_Mammadov875×652 29.2 KB

Challenge #19_Tural_Mammadov.xlsx (8.3 MB)

1 Like
3

HI @IlgarZarbaliyev ,

Here is my solution to this workout.

New Mexico:
=HLOOKUP($A15,$A$1:$M$11,MATCH(C$14,$A$1:$A$11,0),FALSE)
Texas:
=HLOOKUP($A15,$A$1:$M$11,MATCH(B$14,$A$1:$A$11,0),FALSE)

File attached:
Excel Workout Challenge #19 Keith Smith.xlsx (8.3 MB)

Thanks for the workout
Keith

1 Like
4

Texas:
=HLOOKUP($A15,$B$1:$M$11,MATCH(B$14,$A$1:$A$11,0),FALSE)

New Mexico:
=HLOOKUP($A15,$B$1:$M$11,MATCH(C$14,$A$1:$A$11,0),FALSE)

Challenge #19
Challenge #19959×509 37.4 KB

EnterpriseDNA Excel Challenge #19 Solution Muhammad Basharat Asghar.xlsx (8.3 MB)

1 Like
5

Thank you for participating in the Excel Challenge related to HLOOKUP & MATCH 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 HLOOKUP & MATCH Functions in Excel.

Here is my solution to Workout #19:

image
image1809×568 82.7 KB

  • For the State selected in B14 Cell:
    =HLOOKUP($A15,$B$1:$M$11,MATCH(B$14,$A$1:$A$11,0),FALSE)

  • For the State selected in C14 Cell:
    =HLOOKUP($A15,$B$1:$M$11,MATCH(C$14,$A$1:$A$11,0),FALSE)

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

1 Like
6

Blockquote[spoiler]

Summary

Challenge #19.xlsx (8.3 MB)

Blockquote

[/spoiler]

1 Like
7

I have utilized named ranges to enhance the simplicity of my formula. The range “Months” encompasses cells B1:M11, while the range “States” pertains to cells A1:A11.

Formula for retrieving value based on Month and State (for both Texas and New Mexico): =HLOOKUP($A15,Months,MATCH(B$14,States,0),FALSE)

Snopshot of the result:

image
image1315×549 42.5 KB

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

1 Like
8

Awesome!