Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|