Multiple IF Need Help

[Sacs]

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,'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

 

[Kassie]

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?

 

[Sacs]

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

 

[David Biddulph]

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.

 

[Kassie]

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.

 

[Kassie]

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.