|
Help with HLOOKUP Please
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! |
Help with HLOOKUP Please
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! |
Help with HLOOKUP Please
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! |
Help with HLOOKUP Please
=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! |
Help with HLOOKUP Please
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! |
Help with HLOOKUP Please
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! |
Help with HLOOKUP Please
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! |
Help with HLOOKUP Please
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! |
Help with HLOOKUP Please
"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 ---- |
Help with HLOOKUP Please
"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 ---- |
Help with HLOOKUP Please
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 ---- |
Help with HLOOKUP Please
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 ---- |
Help with HLOOKUP Please
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! |
Help with HLOOKUP Please
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 ---- |
| All times are GMT +1. The time now is 02:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com