=IF(Lookup Problem

[artistry]

hi guys,

i'm new on this forum and new to excel formulas as well, i've been trying to make this simple worksheet using a =IF(Lookup formula to display when specific people are working or are on their days off.

by changing the days specified on cell C8 to display the persons name if they're working that day, or the text "day off" if they aren't.

but somehow whenever i type in "thu" or "fri" most of the results displayed are showing up as "#N/A"

i'm going to include here the actual file is anyone is able to help me, as i can't seem to work it out for the life of me. (download)

by the way, the formula i'm using is:

=IF(LOOKUP($C$8;E9:K9)=$C$8;"day off";E9)

thank you in advance

 

[VoG]

There are no #N/A in the file that I downloaded

 

[artistry]

it's been happening to me when you write different days on cell C8 - mon, tue, wed, thu, fri, sat, sun (above the names column, it's displaying #N/A on some of the names

for example on "fri" most of the names show up as #N/A instead of the actual name or the "day off" designation

i'm at a complete loss with this

 

[T. Valko]

it's been happening to me when you write different days on cell C8 - mon, tue, wed, thu, fri, sat, sun (above the names column, it's displaying #N/A on some of the names

for example on "fri" most of the names show up as #N/A instead of the actual name or the "day off" designation

i'm at a complete loss with this

=IF(LOOKUP($C$8;E9:K9)=$C$8;"day off";E9)

I didn't look at your file...

For that formula to work properly the lookup vector E9:K9 must be sorted in ascending order.

 

[artistry]

sorry, I copied the formula wrong actually,

the one that I'm using is:

=IF(LOOKUP($C$8;E9:K9)=$C$8;"day off";D9
(D9 - that changes according the E8:K8, E9:K9, etc...) has the name assigned to it, so if the day that you choose is not one of the days off, then it will display the text inside of that cell

I do apologise but I don't understand what you meant by ascending order, since it will not let me write the formula any other way :\

 

[Haseeb Avarakkan]

Hello,

Try, C9 copy down.

=IF(HLOOKUP(C$8,$E$8:$K$17,ROWS(C$9:C9)+1,0)=C$8,"day off",D9)

 

[artistry]

sorry Haseeb Avarakkan, tried that formula but it wouldn't work

 

[T. Valko]

sorry Haseeb Avarakkan, tried that formula but it wouldn't work

Try this...

Entered in C9 and copied down...

=IF(COUNTIF(E9:K9;C$8);"day off";D9)

 

[Haseeb Avarakkan]

sorry Haseeb Avarakkan, tried that formula but it wouldn't work

Try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">sun</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">mon</td><td style="font-weight: bold;text-align: center;;">tue</td><td style="font-weight: bold;text-align: center;;">wed</td><td style="font-weight: bold;text-align: center;;">thu</td><td style="font-weight: bold;text-align: center;;">fri</td><td style="font-weight: bold;text-align: center;;">sat</td><td style="font-weight: bold;text-align: center;;">sun</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Matt</td><td style="text-align: center;;">Matt</td><td style="text-align: center;;">Matt</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">fri</td><td style="text-align: center;;">sat</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Keri</td><td style="text-align: center;;">Keri</td><td style="text-align: center;;">Keri</td><td style="text-align: center;;"></td><td style="text-align: center;;">tue</td><td style="text-align: center;;">wed</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Kenny</td><td style="text-align: center;;">Kenny</td><td style="text-align: center;;">Kenny</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">fri</td><td style="text-align: center;;">sat</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Lizzie</td><td style="text-align: center;;">Lizzie</td><td style="text-align: center;;">Lizzie</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">fri</td><td style="text-align: center;;">sat</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Day Off</td><td style="text-align: center;;">Day Off</td><td style="text-align: center;;">Mandy</td><td style="text-align: center;;">mon</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">sun</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Day Off</td><td style="text-align: center;;">Day Off</td><td style="text-align: center;;">Eddie</td><td style="text-align: center;;">mon</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">sun</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Arthur</td><td style="text-align: center;;">Arthur</td><td style="text-align: center;;">Arthur</td><td style="text-align: center;;"></td><td style="text-align: center;;">tue</td><td style="text-align: center;;">wed</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Day Off</td><td style="text-align: center;;">Day Off</td><td style="text-align: center;;">Gary</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">sat</td><td style="text-align: center;;">sun</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Day Off</td><td style="text-align: center;;">Day Off</td><td style="text-align: center;;">Richard</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">sat</td><td style="text-align: center;;">sun</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">C$8,E9:K9,0</font>)</font>),"Day Off",D9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C9</th><td style="text-align:left">=IF(<font color="Blue">HLOOKUP(<font color="Red">C$8,$E$8:$K$17,ROWS(<font color="Green">C$9:C9</font>)+1,0</font>)=C$8,"Day Off",D9</font>)</td></tr></tbody></table></td></tr></table><br />

Change the "," with ";"

 

[artistry]

thanks guys, seem to finally have been able to get it to work, thanks to T. Valko's formula this was really doing my head in...

will try yours now Haseeb Avarakkan, to see if i can get it to work as i would like to learn how to get some of these to actually function