HLOOKUP Formula

[hyline]

Hi
I am creating a pricing spreadsheet and can't get a lookup formula to work. At the top of the selected column I enter a "y".

I need the lookup formula to select the number in the column 4 rows below. I keep getting a #REF.

My formula looks like this =HLOOKUP("y",A4:A9,4,FALSE)

I've tried all sorts of help menus and websites - I can't see why it won't work.

Any help would be greatly appreciated.

Thanks

 

[jeffreybrown]

Hello & Welcome to the Board,

How is your data setup compared to what I have below?

I used =HLOOKUP("y",A4:A9,4,FALSE) and it returns 3.
<TABLE style="WIDTH: 54pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=72><COLGROUP><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2732802 class=xl65 height=21 width=72>y</TD></TR>

<TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2732802 class=xl65 height=21>1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>3</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>4</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>5</TD></TR></TBODY></TABLE>

 

[hyline]

Hopefully this screenshot will work...

-- removed inline image ---

 

[Peter_SSs]

Welcome to the MrExcel board!

Your basic problem is that your "y" is in column I but your HLOOKUP formula is only looking in column A.

Without knowing exactly which cell in column I the "y" is in (is it I1 or I4 or something else) and without knowing what else is on your sheet and what you are trying to achieve, it is a bit hard to be sure what you need, but try something like this:

=HLOOKUP("y",A4:I9,4,0)

For the future, if you want to post a small screen shot, my signature block contains 3 possible methods. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

 

[hyline]

Thanks for your help.
Not sure if this is the done thing, but the following link is to a copy of my spreadsheet on a public FTP.

http://dl.dropbox.com/u/27080794/Book1.xls

Andrew

 

[Peter_SSs]

Not sure if this is the done thing, but the following link is to a copy of my spreadsheet on a public FTP.

http://dl.dropbox.com/u/27080794/Book1.xls

Andrew

Andrew

Such a link is not really breaking any of our rules but below is my usual 'template text' advice to members who do so:

Many of the experienced helpers here, including me, choose not to download files from other sites. Also, due to security issues at work sites, many users are unable to download such files.

You will get many more potential helpers if you explain your problem clearly in words and, if needed, post a small screen shot or two directly in your post. My signature block below suggests 3 ways you can do that. Test them out in the Test Here forum.

Did my suggested forumula change solve your issue?

 

[hyline]

-- removed inline image ---


Thank you for your persistence.

On my actual spreadsheet the selection here is Cols D through H starting at Row 4. Selecting y in this instanace should result in the bolded 32,000.

My formula again - =HLOOKUP("y",D4:H4,6,FALSE)

Andrew

 

[T. Valko]

-- removed inline image ---


Thank you for your persistence.

On my actual spreadsheet the selection here is Cols D through H starting at Row 4. Selecting y in this instanace should result in the bolded 32,000.

My formula again - =HLOOKUP("y",D4:H4,6,FALSE)

Andrew

I your sample file the "y" corresponds to 0.

Try one of these:

=SUMIF(D4:H4,"y",D8:H8)

=INDEX(D8:H8,MATCH("y",D4:H4,0))