Lookup Formula HELP!

Mickey66

New Member
Joined
Mar 25, 2011
Messages
7
Can anyone tell me what's the logic behind of this formula? I want to add another box beside of "20 days" and name it "25 days", so when the red check mark appears beside of the "25 days", the yellow highlighted number will give me "25". Currently it gives me "10" by default if 10 days is selected or nothing is selected.

The formula in that yellow highlighted cell is =LOOKUP("ü",M21:S21,{0,0,0,15,10,0,20}), the red check mark is actually the symbol ü.

I tried to change it to =LOOKUP("ü",M21:V21,{0,0,0,15,10,0,20,0,0,25}),
but it does not work...

<TABLE style="WIDTH: 306pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=408 border=0 x:str><COLGROUP><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" span=17 width=24><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=24 height=34></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24>K</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24>L</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24>M</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24>N</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24>O</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24>P</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24>Q</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24>R</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24>S</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=24></TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" colSpan=5 height=34>ROW 21</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>10 days</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ü</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=3>15 days</TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=3>20 days</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" align=right x:fmla='=LOOKUP("ü",I2:O2,{0,0,0,15,10,0,20})' x:num>10</TD></TR></TBODY></TABLE>
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
try this

=CHOOSE(MATCH("ü",J21:V21,0),0,0,15,10,0,20,0,0,25)

<TABLE style="WIDTH: 324pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=431 border=0 x:str><COLGROUP><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2687760 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 29pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=38 height=17> </TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=21 x:num>10</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 26pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=35>days</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>ü

</TD><TD class=xl25 id=td_post_2688178 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=21 x:num>15</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 26pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=35>days</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=45> </TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=21 x:num>20</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 26pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=35>days</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 32pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=43> </TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=21 x:num>25</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 26pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=35>days</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 27pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=36> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=21 x:num x:fmla='=CHOOSE(MATCH("ü",A1:M1,0),0,0,15,10,0,20,0,0,25)'>10</TD></TR></TBODY></TABLE>
 
Last edited:
Upvote 0
try this

=CHOOSE(MATCH("ü",J21:V21,0),0,0,15,10,0,20,0,0,25)

<TABLE style="WIDTH: 324pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=431 border=0 x:str><COLGROUP><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2687760 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 29pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=38 height=17></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=21 x:num>10</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 26pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=35>days</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 18pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=24>ü


</TD><TD class=xl25 id=td_post_2688178 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=21 x:num>15</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 26pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=35>days</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=45></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=21 x:num>20</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 26pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=35>days</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 32pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=43></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=21 x:num>25</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 26pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=35>days</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 27pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=36></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=21 x:num x:fmla='=CHOOSE(MATCH("ü",A1:M1,0),0,0,15,10,0,20,0,0,25)'>10</TD></TR></TBODY></TABLE>


Thank you Texasalynn! This one only works for "10 days" selection..
I should have mentioned that the red check mark needs to be in either column M, P, S, V. If nothing is selected, the yellow number returns to "10" by default
 
Upvote 0
Thank you Texasalynn! This one only works for "10 days" selection..
I should have mentioned that the red check mark needs to be in either column M, P, S, V. If nothing is selected, the yellow number returns to "10" by default
Change Texasalynn's formula to this:

=CHOOSE(MATCH("ü",M21:V21,0),10,,,15,,,20,,,25)
 
Upvote 0
Try this...

=IF(COUNT(MATCH("ü",M21:V21,0)),CHOOSE(MATCH("ü",M21:V21,0),10,,,15,,,20,,,25),"")

Thank you very much! This one works very well! by the way, I changed it to

=IF(COUNT(MATCH("i",M21:V21,0)),CHOOSE(MATCH("i",M21:V21,0),10,,,15,,,20,,,25),"10") :)

Very nice, thank you again!
 
Upvote 0
Thank you very much! This one works very well! by the way, I changed it to

=IF(COUNT(MATCH("i",M21:V21,0)),CHOOSE(MATCH("i",M21:V21,0),10,,,15,,,20,,,25),"10") :)

Very nice, thank you again!
Ooops!

Yeah, I forgot you wanted 10 to be returned.

One thing, though, remove the quotes from around the 10:

=IF(COUNT(MATCH("i",M21:V21,0)),CHOOSE(MATCH("i",M21:V21,0),10,,,15,,,20,,,25),10)

When you quote numbers then Excel will evaluate that as a TEXT entry and not a numeric entry.
 
Upvote 0
Ooops!

Yeah, I forgot you wanted 10 to be returned.

One thing, though, remove the quotes from around the 10:

=IF(COUNT(MATCH("i",M21:V21,0)),CHOOSE(MATCH("i",M21:V21,0),10,,,15,,,20,,,25),10)

When you quote numbers then Excel will evaluate that as a TEXT entry and not a numeric entry.

Oooo~~ Great stuff! Got it!
 
Upvote 0

Forum statistics

Threads
1,224,508
Messages
6,179,188
Members
452,893
Latest member
denay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top