Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating Vlookup in a IF Statement
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating Vlookup in a IF Statement
In general: if(isna(vlookup(...)),"missing",vlookup())
The vlookup is just like normal, something like vlookup(a1,Sheet2!a:b,2,false) "Shams" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating Vlookup in a IF Statement
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(V LOOKUP(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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=504619 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating Vlookup in a IF Statement
Shams Wrote: 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". 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 following statement.....etc. If not, what happens if it isn't found in either table? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=504619 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating Vlookup in a IF Statement
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. "bpeltzer" wrote: In general: if(isna(vlookup(...)),"missing",vlookup()) The vlookup is just like normal, something like vlookup(a1,Sheet2!a:b,2,false) "Shams" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating Vlookup in a IF Statement
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 "Shams" wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating Vlookup in a IF Statement
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") "Shams" wrote: 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. "bpeltzer" wrote: In general: if(isna(vlookup(...)),"missing",vlookup()) The vlookup is just like normal, something like vlookup(a1,Sheet2!a:b,2,false) "Shams" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Statement linked to cell with VLOOKUP problem - getting wrong v | Excel Worksheet Functions | |||
Retry: VLOOKUP nested in IF Statement | Excel Worksheet Functions | |||
vlookup inside an if statement? | Excel Worksheet Functions | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
Vlookup of an if statement return | Excel Worksheet Functions |