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
Frank FreeseAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor 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
 
Shanan212Commented:
=SUM(INDIRECT(ADDRESS(3,MATCH($C$2,$E$2:$H$2,0)+4)&":"&ADDRESS(6,MATCH($C$2,$E$2:$H$2,0)+4)))

Open in new window


Try that on A3 and just press enter. What are you really trying to do in that cell?
0
 
Frank FreeseAuthor 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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Shanan212Commented:
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
 
Frank FreeseAuthor Commented:
The example I was using from another source is incorrect. You answered my question and thank you
0
 
Frank FreeseAuthor 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
 
Shanan212Commented:
No worries about points. I am glad I could help. Thanks for the feedback :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.