Formula gone awry

[Connie Martin]

This formula was working and it no longer works:

=IF(D163="Yes","",IF(VLOOKUP(C163,NIRAV,2)="","Not
Scheduled",VLOOKUP(C163,NIRAV,2)))

I believe it's an array formula but whether array or not, it doesn't work.

In D163 it says "No". In C163 is the number 12639. That number is also in
a named range NIRAV in the first column of that range. In column 2 of the
range is the date Nov-24-08, but this formula is returning "Not Scheduled".

Hope that's enough info for someone to please help me out with this. This
was a great tool until it quit working and I don't know why it quit working.
I've tried it both ways---as an array formula and without the
CTRL+SHIFT+ENTER. I get the exact same answers---wrong ones!

Help, please. Connie

 

[Gary''s Student]

I think the problem is not with the formula, but with cell D163.

Re-verify the contents are "No"

 

[Pete_UK]

Connie,

it is not an array formula, so no need for CSE.

In omitting the 4th (optional) parameter for VLOOKUP, you are implying
that the lookup table NIRAV is sorted - is this the case? If not then
you should change the formula to:

=IF(D163="Yes","",IF(VLOOKUP(C163,NIRAV,2,0)="","Not Scheduled",VLOOKUP
(C163,NIRAV,2,0)))

and this forces the function to look for an exact match.

Another area where problems might occur is that the number 12639 might
be a text value in the table and a number in C163 - these need to
match exactly.

Hope ths helps.

Pete

 

[Sheeloo]

Your formula is correct and it is NOT an Array Formula...

Most likely the numbers in first column of NIRAV are NOT being treated as
such..

Just to test Copy the cell containing 12639 and past in the cell with that
number in NIRAV... you should get the answer you expect...

 

[Connie Martin]

Definitely "No" is in D163. If it were "Yes" then the formula should return
nothing, but it's returning "Not Scheduled". Connie

 

[Connie Martin]

You are a genious! Thank you! I guess when I first did this formula, I
must've sorted the list, but I never receive it sorted so in case I should
forget to sort it, I have adopted your formula below. I tried first to see
if it were an issue with the columns not being formatted as a number but that
didn't help whether they were or not, so it had to do with the sorting.
Thank you so much! Connie

 

[Connie Martin]

Thank you, Sheeloo, but it was a problem of the NIRAV column not being
sorted, as in Pete's answer. Thank you for taking the time to repsond. I
really do appreciate all the help I get here. Connie

 

[Sheeloo]

You are welcome... I had noticed that the fourth parameter was not there but
I thought (wrongly) that default value is FALSE...
Sorry about that.

Glad it worked out for you...

 

[Shane Devenshire]

Sheeloo,

You are not entirely "thinking wrongly"

If

=VLOOKUP(A1,Table,2,) is entered Excel assumes the 4th argument is False
but
=VLOOKUP(A1,Table,2) is entered Excel assumes the 4th argument is TRUE

Cheers,
Shane Devenshire

 

[Pete_UK]

Thanks for feeding back, Connie - glad to hear that one of the
suggestions worked for you.

In changing the formula to look for an exact match though, this might
not do exactly what you want it to do. If the 4th parameter is set to
TRUE (or missing) and if the table is sorted then an exact match does
not need to be found but the formula will not report an error - it
will match with the nearest number which is less than the lookup
value. This might have been how you originally wanted the formula to
perform.

You will need to consider if the change to an exact lookup is still
appropriate.

Pete

 

[Sheeloo]

Thanks...

I did not know that...

Sheeloo,

You are not entirely "thinking wrongly"

If

=VLOOKUP(A1,Table,2,) is entered Excel assumes the 4th argument is False
but
=VLOOKUP(A1,Table,2) is entered Excel assumes the 4th argument is TRUE

Cheers,
Shane Devenshire