Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 251
Default Formula gone awry

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Formula gone awry

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

Re-verify the contents are "No"
--
Gary''s Student - gsnu200813


"Connie Martin" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Formula gone awry

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)="","N ot 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

On Nov 12, 7:17*pm, Connie Martin
wrote:
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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default Formula gone awry

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" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 251
Default Formula gone awry

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

"Gary''s Student" wrote:

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

Re-verify the contents are "No"
--
Gary''s Student - gsnu200813


"Connie Martin" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 251
Default Formula gone awry

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

"Pete_UK" wrote:

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)="","N ot 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

On Nov 12, 7:17 pm, Connie Martin
wrote:
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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 251
Default Formula gone awry

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" wrote:

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" wrote:

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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default Formula gone awry

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...

"Connie Martin" wrote:

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" wrote:

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" wrote:

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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Formula gone awry

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


"Sheeloo" wrote:

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...

"Connie Martin" wrote:

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" wrote:

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" wrote:

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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Formula gone awry

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

On Nov 12, 7:57*pm, Connie Martin
wrote:
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



"Pete_UK" wrote:
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)="","N ot 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


On Nov 12, 7:17 pm, Connie Martin
wrote:
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- Hide quoted text -


- Show quoted text -




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default Formula gone awry

Well done, Shane -- This is a good thing to point out about VLOOKUP. Thanks.

Shane Devenshire wrote:
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

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default Formula gone awry

Thanks...

I did not know that...

"Shane Devenshire" wrote:

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


"Sheeloo" wrote:

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...

"Connie Martin" wrote:

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" wrote:

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" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"