Incorporating Vlookup in a IF Statement

S

Shams

Folks,
I have the following 2 tables:

Table 1:

Ticket # Gross Fare

2262945423 $628.24
2262945413 $1,029.16
2262945419 $1,029.16
2262945420 $1,029.16
2262945424 $1,029.16
2262945414 $2,815.11


Table 2:

Tckt# Gross Fare

2262945413 $1,029.16
2262945414 $2,815.11
2262945419 $1,029.16
2262945420 $1,029.16
2262945421 $2,892.40
2262945422 $2,892.40
2262945423 $628.24
2262945424 $1,029.16
2262945503 $908.84
2262945504 $2,887.05
2262945505 $2,588.91
2262945506 $1,029.16
2262945548 $2,588.91
2262945557 $55.10


Can I use a If statement to do a vlookup based on Tckt # in Table 1 and
search for it in Table 2 and if it finds the number in Table 2 then return
the following statement: " Not allocated - To be done".

I know that I can break out the function in two distinct processes but it
would be more efficient if I could consolidate them in one function. I will
appreciate any suggestions on this issue. Thank you.

Regards,
Shams.
 
B

bpeltzer

In general: if(isna(vlookup(...)),"missing",vlookup())
The vlookup is just like normal, something like vlookup(a1,Sheet2!a:b,2,false)
 
S

SteveG

Shams,

This should do it for you. Assuming that A2 is the value in Table 1
you are looking for in Table 2 which is D2:E15.

=IF(ISNA(VLOOKUP(A2,$D$2:$E$15,1,FALSE)>0),"",IF(VLOOKUP(A2,$D$2:$E$15,1,FALSE)>0,"Not
allocated - To be done"))

The first IFprevents you from seeing the N/A error if the number is not
in Table 2.

HTH

Steve
 
D

daddylonglegs

Shams said:
Can I use a If statement to do a vlookup based on Tckt # in Table
and
search for it in Table 2 and if it finds the number in Table 2 the
return
the following statement: " Not allocated - To be done".
Click to expand...

Can you clarify?

What happens if you find the number in table 1?

Do you mean if it DOESN'T find number in table 2 return the followin
statement.....etc.

If not, what happens if it isn't found in either table
 
S

Shams

I tried to incorporate your suggestion in my formula. It just ends up
returning the $$ figure. Rather, I want it to return the text string that I
am specifying: "To be allocated". So, this is what I did:

Assume Table 1 is in Sheet 1 (Tckt # in A1) and Table 2 is in Sheet 2. So:

=if(isna(vlookup(A1, Sheet2!A:B,2,false)), "To be allocated",vlookup(A1,
Sheet2!A:B,2,false))

Using the above formula, I am getting the $$ figure thus confirming that
vlookup found the value. I want it to go one step further....i.e. evaluate
it and if it finds the number then print "To be allocated".

Am I asking for something needlessly complicated? Thanks.

Shams.
 
S

Shams

Folks,
While you guys were sending me tips, I tried the following formula:

IF(ISNUMBER(VLOOKUP(A277,J:K,2,FALSE)),"To be allocated",0)

In other words, A277 is my tkt# in Table 1 and J:K is my array in Table 2.
So, now it is returning my text if it matches the lookup in J:K.

Am I technically correct in writing the formula as above or is it just a
quirk????

In the meantime I will also try the formula given to me by SteveG
 
B

bpeltzer

No, we may have been reading complication into your request... Similar
queries usually ask how to avoid returning #N/A when the result isn't found;
on closer reading, I think you're only asking to determine whether there's a
match or not. If that's so then try =if(isna(match(A1,
Sheet2!A:A,false)),"","To be allocated")
 
Top