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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Perhaps you'd like to try this:

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Paul Black" wrote in message
...
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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

"Max" wrote in message
...
Perhaps you'd like to try this:

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Paul Black" wrote in message
...
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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"Niek Otten" wrote
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 !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Oops, typo .. apologies
should read as:
Much neater, Niek, thanks !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Paul,

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


"Paul Black" wrote:

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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

J
"Gixxer_J_97" wrote:

Hi Paul,

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


"Paul Black" wrote:

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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

--
Regards,
Tom Ogilvy




"Paul Black" wrote in message
...
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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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)


"Paul Black" wrote in message
...
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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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)


"Bob Phillips" wrote in message
...
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)


"Paul Black" wrote in message
...
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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"Bob Phillips" wrote

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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Caught that afterwards myself Max <vbg

Regards

Bob

"Max" wrote in message
...
"Bob Phillips" wrote

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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Caught that afterwards myself Max <vbg
Urrgh, and you denied me the 2nd gotcha! pleasure <vbg
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----