# Excel Workout 19 - Hlookup & Match Functions!

Welcome to Excel Workout #19!
Difficulty Level:

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

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.

• 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

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

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)`

1 Like

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

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)

1 Like

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:

• 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

Blockquote[spoiler]

Summary

Challenge #19.xlsx (8.3 MB)

Blockquote

[/spoiler]

1 Like

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:

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

1 Like

Awesome!