HLOOKUP problem

Folks,
I'm having a problem with HLOOKUP. My referenced cell is correct and my array looks to be ok
=HLOOKUP(\$C\$2,\$E\$2:\$H\$6,(ROW()))

However, the data being return does not include the second item in the array, \$E\$3 but starts at \$E\$4?
Hlookup-problem.xlsm
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
``````=SUM(INDIRECT(ADDRESS(3,MATCH(\$C\$2,\$E\$2:\$H\$2,0)+4)&":"&ADDRESS(6,MATCH(\$C\$2,\$E\$2:\$H\$2,0)+4)))
``````

Try that on A3 and just press enter. What are you really trying to do in that cell?
0
Commented:
Your formulas start in row 3, so your first formula tries to return the value from the third row of the table not the third row of the sheet. Try:

=HLOOKUP(\$C\$2,\$E\$2:\$H\$6,ROW()-1)
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
I'm building a workbook for my son with some functions and formulas to learn and this is one of them. Why is the HLOOKUP function I'm using returning values beginning  with \$E\$4 and not \$E\$3?
0
Commented:
because the "row()" would return the current row where its located. In your case, your formula is located in row "A3" hence its row 3.

So when used inside Hlookup, it returns the 3rd value (horizontally) from the date desired...the 3rd value from E2 is E4.

Like Rorya said, if you want the E3 returned, then you can deduct 1 from the row to return that value.
0
Author Commented:
The example I was using from another source is incorrect. You answered my question and thank you
0
Author Commented:
shanan212,
That is a great observation. I'm working on another example and ran into the same problem. It wasn't until I read you comment that I began to understand the conditions for ROW().
Thanks - I am very grateful.
By the way, I did have a solution for the Sum already that looks to work.
I'll be back for more help
I can't take back points but if I could I'd split them 50/50 - possible?
0
Commented:
No worries about points. I am glad I could help. Thanks for the feedback :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.