Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple IF Need Help
This is the formula
has to do with getting a field for people names with variance i.e. c2 (last name,first Name) or b2(first name, last Name) or A2 (last name, first name only partial name) the formula works but what is happening in the part that it looks up the correct answer VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0) portion it shows the correct answer but in the cell it shows #N/A ... if I reverse the formula to start with A2 ..then b2 then c2 ... I get the same problem in this case the answer id "Ad Dhubi' which A2 picks up but show $N/A in the cell. What is wrong with the formula and why is it not showing "Ad Dhubi" The main purpose is pick up the answer with names such as John Henri Walker when I download info from the internet the name downloaded could be Henri John Walker or Walker John Henri or Walker Henri John which is why my vlookup look for the correct name and then give the answer .. I've double check name scenerios and the A2 portion does pick up the answer in the formal bar but the cell still show #N/A ... Here is the complete formula =IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)),VLOOKUP(B2,'EURO ENTRY LIST'!$C$1:$D$1500,2,0),IF(ISERROR(VLOOKUP(C2,'EUR O ENTRY LIST'!$B$1:$D$1500,2,0)),VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0),VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,3,0))) Please Help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple IF Need Help
Not only is your info limited, it is also confusing? Your formula checks
whether C2 shows an error, then does a vlookup on B2, which most likely also creates an error. No error trapping is done though. Then you again check C2 for an error, in which case you want to look up A2, but if C2 is not an error, you want to look up C2. This logic will not work, the way I see it. Look at your original formula, as I have split it into IF - THEN - ELSE, and you will perhaps understand your problem? -- Hth Kassie Kasselman Change xxx to hotmail "Sacs" wrote: This is the formula has to do with getting a field for people names with variance i.e. c2 (last name,first Name) or b2(first name, last Name) or A2 (last name, first name only partial name) the formula works but what is happening in the part that it looks up the correct answer VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0) portion it shows the correct answer but in the cell it shows #N/A ... if I reverse the formula to start with A2 ..then b2 then c2 ... I get the same problem in this case the answer id "Ad Dhubi' which A2 picks up but show $N/A in the cell. What is wrong with the formula and why is it not showing "Ad Dhubi" The main purpose is pick up the answer with names such as John Henri Walker when I download info from the internet the name downloaded could be Henri John Walker or Walker John Henri or Walker Henri John which is why my vlookup look for the correct name and then give the answer .. I've double check name scenerios and the A2 portion does pick up the answer in the formal bar but the cell still show #N/A ... Here is the complete formula =IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)), THEN VLOOKUP(B2,'EURO ENTRY LIST'!$C$1:$D$1500,2,0), ELSE IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)),VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0),VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,3,0))) Please Help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple IF Need Help
Ok ... I get the sense of what your saying here is it in logical order cause
when the formula says false it does not put the info in the cell it just states false which is why I repeat it at end of the formula .... which part don't I need. ? =IF(ISERROR(VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0)), (I GET TRUE) ELSE VLOOKUP(B2,Sheet1!$B$1:$M$1500,4,0), (I GET TRUE) THEN IF(ISERROR(VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0)), (SHOULD IT SAY B2 INSTEAD) ELSE VLOOKUP(C2,Sheet1!$C$1:$M$1500,3,0), (I GET FALSE) BUT #N/A IS SHOWN IN THE CELL IF I DON'T ADD THIS ONE NOTHING GETS PUT IN THE CELL VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0))) "Kassie" wrote: Not only is your info limited, it is also confusing? Your formula checks whether C2 shows an error, then does a vlookup on B2, which most likely also creates an error. No error trapping is done though. Then you again check C2 for an error, in which case you want to look up A2, but if C2 is not an error, you want to look up C2. This logic will not work, the way I see it. Look at your original formula, as I have split it into IF - THEN - ELSE, and you will perhaps understand your problem? -- Hth Kassie Kasselman Change xxx to hotmail "Sacs" wrote: This is the formula has to do with getting a field for people names with variance i.e. c2 (last name,first Name) or b2(first name, last Name) or A2 (last name, first name only partial name) the formula works but what is happening in the part that it looks up the correct answer VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0) portion it shows the correct answer but in the cell it shows #N/A ... if I reverse the formula to start with A2 ..then b2 then c2 ... I get the same problem in this case the answer id "Ad Dhubi' which A2 picks up but show $N/A in the cell. What is wrong with the formula and why is it not showing "Ad Dhubi" The main purpose is pick up the answer with names such as John Henri Walker when I download info from the internet the name downloaded could be Henri John Walker or Walker John Henri or Walker Henri John which is why my vlookup look for the correct name and then give the answer .. I've double check name scenerios and the A2 portion does pick up the answer in the formal bar but the cell still show #N/A ... Here is the complete formula =IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)), THEN VLOOKUP(B2,'EURO ENTRY LIST'!$C$1:$D$1500,2,0), ELSE IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)),VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0),VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,3,0))) Please Help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple IF Need Help
Excel's IF formula doesn't have THEN and ELSE as part of the syntax. Excel
help will tell you the syntax and give examples. -- David Biddulph Sacs wrote: Ok ... I get the sense of what your saying here is it in logical order cause when the formula says false it does not put the info in the cell it just states false which is why I repeat it at end of the formula .... which part don't I need. ? =IF(ISERROR(VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0)), (I GET TRUE) ELSE VLOOKUP(B2,Sheet1!$B$1:$M$1500,4,0), (I GET TRUE) THEN IF(ISERROR(VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0)), (SHOULD IT SAY B2 INSTEAD) ELSE VLOOKUP(C2,Sheet1!$C$1:$M$1500,3,0), (I GET FALSE) BUT #N/A IS SHOWN IN THE CELL IF I DON'T ADD THIS ONE NOTHING GETS PUT IN THE CELL VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0))) "Kassie" wrote: Not only is your info limited, it is also confusing? Your formula checks whether C2 shows an error, then does a vlookup on B2, which most likely also creates an error. No error trapping is done though. Then you again check C2 for an error, in which case you want to look up A2, but if C2 is not an error, you want to look up C2. This logic will not work, the way I see it. Look at your original formula, as I have split it into IF - THEN - ELSE, and you will perhaps understand your problem? -- Hth Kassie Kasselman Change xxx to hotmail "Sacs" wrote: This is the formula has to do with getting a field for people names with variance i.e. c2 (last name,first Name) or b2(first name, last Name) or A2 (last name, first name only partial name) the formula works but what is happening in the part that it looks up the correct answer VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0) portion it shows the correct answer but in the cell it shows #N/A ... if I reverse the formula to start with A2 ..then b2 then c2 ... I get the same problem in this case the answer id "Ad Dhubi' which A2 picks up but show $N/A in the cell. What is wrong with the formula and why is it not showing "Ad Dhubi" The main purpose is pick up the answer with names such as John Henri Walker when I download info from the internet the name downloaded could be Henri John Walker or Walker John Henri or Walker Henri John which is why my vlookup look for the correct name and then give the answer .. I've double check name scenerios and the A2 portion does pick up the answer in the formal bar but the cell still show #N/A ... Here is the complete formula =IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)), THEN VLOOKUP(B2,'EURO ENTRY LIST'!$C$1:$D$1500,2,0), ELSE IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)),VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0),VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,3,0))) Please Help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple IF Need Help
OK, to start off with, I put in the TEHN and ELSE to help you order your
thoughts only. It is not part of the normal IF statement. In actual fact, that is what the comma's do, split the statement into if ... then ... else. If I can elaborate. Your formula begins with checking whether a VLOOKUP on cell A2 ( in the other sheet) results in an error, or then, if that lookup cannot find a result. If this is the case, then your formula performs a VLOOKUP on cell B2, (in another range on the other sheet). If this VLOOKUP gets a result, you should have a result. If however, this VLOOKUP also equates to an error, you formula bombs out with an #N/A. This happens because you did not do error trapping in this portion of your formula. To now try and recheck the first part of the formula is senseless, since it has already been established that this first portion is an error. At the same time, your composition of your formula will not get to the stage where it will recheck the first portion. I say again, your info on exactly how your data is set up, is extremely limited. I can therefore not tell you exactly how to phrase your formula. What you should however do, the way I see it based on the limited info available, is to check for an error in the first lookup, then check for an error in the second lookup, then check for an error in the third lookup, otherwise show nothing or something else. Contact me directly if you want further guidance. -- Hth Kassie Kasselman Change xxx to hotmail "Sacs" wrote: Ok ... I get the sense of what your saying here is it in logical order cause when the formula says false it does not put the info in the cell it just states false which is why I repeat it at end of the formula .... which part don't I need. ? =IF(ISERROR(VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0)), (I GET TRUE) ELSE VLOOKUP(B2,Sheet1!$B$1:$M$1500,4,0), (I GET TRUE) THEN IF(ISERROR(VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0)), (SHOULD IT SAY B2 INSTEAD) ELSE VLOOKUP(C2,Sheet1!$C$1:$M$1500,3,0), (I GET FALSE) BUT #N/A IS SHOWN IN THE CELL IF I DON'T ADD THIS ONE NOTHING GETS PUT IN THE CELL VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0))) "Kassie" wrote: Not only is your info limited, it is also confusing? Your formula checks whether C2 shows an error, then does a vlookup on B2, which most likely also creates an error. No error trapping is done though. Then you again check C2 for an error, in which case you want to look up A2, but if C2 is not an error, you want to look up C2. This logic will not work, the way I see it. Look at your original formula, as I have split it into IF - THEN - ELSE, and you will perhaps understand your problem? -- Hth Kassie Kasselman Change xxx to hotmail "Sacs" wrote: This is the formula has to do with getting a field for people names with variance i.e. c2 (last name,first Name) or b2(first name, last Name) or A2 (last name, first name only partial name) the formula works but what is happening in the part that it looks up the correct answer VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0) portion it shows the correct answer but in the cell it shows #N/A ... if I reverse the formula to start with A2 ..then b2 then c2 ... I get the same problem in this case the answer id "Ad Dhubi' which A2 picks up but show $N/A in the cell. What is wrong with the formula and why is it not showing "Ad Dhubi" The main purpose is pick up the answer with names such as John Henri Walker when I download info from the internet the name downloaded could be Henri John Walker or Walker John Henri or Walker Henri John which is why my vlookup look for the correct name and then give the answer .. I've double check name scenerios and the A2 portion does pick up the answer in the formal bar but the cell still show #N/A ... Here is the complete formula =IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)), THEN VLOOKUP(B2,'EURO ENTRY LIST'!$C$1:$D$1500,2,0), ELSE IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)),VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0),VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,3,0))) Please Help |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple IF Need Help
OK, to start off with, I put in the THEN and ELSE to help you order your
thoughts only. It is not part of the normal IF statement. In actual fact, that is what the comma's do, split the statement into if ... then ... else. If I can elaborate. Your formula begins with checking whether a VLOOKUP on cell A2 ( in the other sheet) results in an error, or then, if that lookup cannot find a result. If this is the case, then your formula performs a VLOOKUP on cell B2, (in another range on the other sheet). If this VLOOKUP gets a result, you should have a result. If however, this VLOOKUP also equates to an error, you formula bombs out with an #N/A. This happens because you did not do error trapping in this portion of your formula. To now try and recheck the first part of the formula is senseless, since it has already been established that this first portion is an error. At the same time, your composition of your formula will not get to the stage where it will recheck the first portion. I say again, your info on exactly how your data is set up, is extremely limited. I can therefore not tell you exactly how to phrase your formula. What you should however do, the way I see it based on the limited info available, is to check for an error in the first lookup, if none is found, then do that lookup, else check for an error in the second lookup, and if no error is found, do that lookup, else do the third lookup, if you are sure that that will not result in an error. Something like: IF(NOT(ISERROR(VLOOKUP(your lookup string))),VLOOKUP(your lookup string),IF(NOT(ISERROR(VLOOKUP(your 2nd lookup string))),VLOOKUP(your 2nd lookup string),VLOOKUP(your 3rd lookup string))). This surmises that your 3rd lookup will never equate to an error. If an error can be expected here as well, you should include errortrapping again, and then, if an error is still found, set the value to eg "", or to "NOT FOUND" or some such string. -- Hth Kassie Kasselman Change xxx to hotmail "Sacs" wrote: Ok ... I get the sense of what your saying here is it in logical order cause when the formula says false it does not put the info in the cell it just states false which is why I repeat it at end of the formula .... which part don't I need. ? =IF(ISERROR(VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0)), (I GET TRUE) ELSE VLOOKUP(B2,Sheet1!$B$1:$M$1500,4,0), (I GET TRUE) THEN IF(ISERROR(VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0)), (SHOULD IT SAY B2 INSTEAD) ELSE VLOOKUP(C2,Sheet1!$C$1:$M$1500,3,0), (I GET FALSE) BUT #N/A IS SHOWN IN THE CELL IF I DON'T ADD THIS ONE NOTHING GETS PUT IN THE CELL VLOOKUP(A2,Sheet1!$A$1:$M$1500,5,0))) "Kassie" wrote: Not only is your info limited, it is also confusing? Your formula checks whether C2 shows an error, then does a vlookup on B2, which most likely also creates an error. No error trapping is done though. Then you again check C2 for an error, in which case you want to look up A2, but if C2 is not an error, you want to look up C2. This logic will not work, the way I see it. Look at your original formula, as I have split it into IF - THEN - ELSE, and you will perhaps understand your problem? -- Hth Kassie Kasselman Change xxx to hotmail "Sacs" wrote: This is the formula has to do with getting a field for people names with variance i.e. c2 (last name,first Name) or b2(first name, last Name) or A2 (last name, first name only partial name) the formula works but what is happening in the part that it looks up the correct answer VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0) portion it shows the correct answer but in the cell it shows #N/A ... if I reverse the formula to start with A2 ..then b2 then c2 ... I get the same problem in this case the answer id "Ad Dhubi' which A2 picks up but show $N/A in the cell. What is wrong with the formula and why is it not showing "Ad Dhubi" The main purpose is pick up the answer with names such as John Henri Walker when I download info from the internet the name downloaded could be Henri John Walker or Walker John Henri or Walker Henri John which is why my vlookup look for the correct name and then give the answer .. I've double check name scenerios and the A2 portion does pick up the answer in the formal bar but the cell still show #N/A ... Here is the complete formula =IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)), THEN VLOOKUP(B2,'EURO ENTRY LIST'!$C$1:$D$1500,2,0), ELSE IF(ISERROR(VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,2,0)),VLOOKUP(A2,'EURO ENTRY LIST'!$A$1:$G$1500,4,0),VLOOKUP(C2,'EURO ENTRY LIST'!$B$1:$D$1500,3,0))) Please Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter | Excel Worksheet Functions | |||
Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
view multiple files in multiple windows on multiple screens. | Excel Discussion (Misc queries) |