Help with HLOOKUP Please

[Paul Black]

Hi,

I have Numbers 1 to 28 in Cells B6:AC6, and Values Associated with these
Numbers in Cells B7:AC7.
I ALSO have Numbers 29 to 56 in Cells B8:AC8, and Values Associated with
these Numbers in Cells B9:AC9.

How do I Create the HLOOKUP Formula in Cell S14 that Looks at the Value
in Cell K14 and then Returns the Value from the HLOOKUP Table. The
Formula Below Works But ONLY for the HLOOKUP Cells B6:AC7.

=IF(ISERROR(HLOOKUP(K14,$B$6:$AC$7,2)),0,(HLOOKUP(K14,$B$6:$AC$7,2)))

Basically, if the Value in Cell K14 is NOT Found in the Above Formula it
Looks in the Formula Below.

=IF(ISERROR(HLOOKUP(K14,$B$8:$AC$9,2)),0,(HLOOKUP(K14,$B$8:$AC$9,2)))

I have Tried Several Variations But to NO Avail.

Thanks in Advance.
All the Best.
Paul

 

[Max]

Perhaps you'd like to try this:

=IF(K14="","",IF(OR(K14<1,K14>56),"Out-of-range",IF(ISERROR(HLOOKUP(K14,$B$6
:$AC$7,2,0)),HLOOKUP(K14,$B$8:$AC$9,2,0),HLOOKUP(K14,$B$6:$AC$7,2,0))))

 

[Guest]

Hi Paul,

try thi
=if(iserror(hlookup(s14,b6:ac7,2,false)),hlookup(s14,b8:ac9,2,false),hlookup(s14,b6:ac7,2,false))

 

[Tom Ogilvy]

=IF(ISNUMBER(MATCH(K14,$B$6:$AC$6,0)),HLOOKUP(K14,$B$6:$AC$7,2),IF(ISERROR(H
LOOKUP(K14,$B$8:$AC$9,2)),0,(HLOOKUP(K14,$B$8:$AC$9,2))))

 

[Guest]

Sorry, s14 should be k1
=if(iserror(hlookup(k14,$b$6:$ac$7,2,false)),hlookup(k14,$b$8:$ac$9,2,false),hlookup(k14,$b$6:$ac$7,2,false))

J

 

[Niek Otten]

But if the numbers are always 1-28 and 29-56, you might as well use

=IF(K14<=28,INDEX(B7:AC7,1,K14),INDEX(B9:AC9,1,K14-28))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Bob Phillips]

How about a different approach Paul

=OFFSET(B6,INT((C14-1)/28)*2+1,IF(MOD(C14,28)=0,28,MOD(C15,28))-1)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

wrong cell ref

=OFFSET(B6,INT((K14-1)/28)*2+1,IF(MOD(K14,28)=0,28,MOD(K14,28))-1)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Max]

But if the numbers are always 1-28 and 29-56, you might as well use
=IF(K14<=28,INDEX(B7:AC7,1,K14),INDEX(B9:AC9,1,K14-28))

Much neater, Nick, thanks !

 

[Max]

How about a different approach Paul

=OFFSET(B6,INT((C14-1)/28)*2+1,IF(MOD(C14,28)=0,28,MOD(C15,28))-1)

Believe Bob meant ... :

=OFFSET(B6,INT((K14-1)/28)*2+1,IF(MOD(K14,28)=0,28,MOD(K14,28))-1)

(Typos corrected <g>)

 

[Bob Phillips]

Caught that afterwards myself Max <vbg>

Regards

Bob

 

[Max]

Caught that afterwards myself Max <vbg>
Urrgh, and you denied me the 2nd gotcha! pleasure <vbg>

 

[Paul Black]

Thats Amazing Guys,

Thanks for the Speedy Replies.
I Tried them ALL and they ALL Work Great.
It just shows How Many Ways you can Achieve a Solution.
I Hope you All had a Great Easter.

All the Best.
Paul

 

[Max]

Oops, typo .. apologies
should read as:

Much neater, Niek, thanks !