Multiple IF statements if nested vlookup function

[octord]

I have this formula:

=IF(R3="Yes","DO NOT COUNT",
IF(OR(D3="Unknown",J3={"Terminated","Active - Kiosk"}),"DO NOT COUNT",
IF(J3="ACTIVE",CONCATENATE(M3,O3),
"Check Record")))

Which works fine, but I need to add one more item that refers to a table to identify "do not count" records. I tried to incorporate the following statement:

IF(VLOOKUP(H2,Pocket!$A$2:$B$213,2,FALSE)="Pocket","DO NOT COUNT",

to look like this:

=IF(R2="Yes","DO NOT COUNT",
IF(OR(D2="Unknown",J2={"Terminated","Active - Kiosk"}),"DO NOT COUNT",
IF(VLOOKUP(H2,Pocket!$A$2:$B$213,2,FALSE)="Pocket","DO NOT COUNT",
IF(J2="ACTIVE",CONCATENATE(M2,O2),
"Check Record"))))

The problem with this is that it returns #N/A because if value is not found on table then it returns the error....i tried different combinations but I just don't seem to come up with the right answer..... can anyone help with some ideas.....PLEASE!!

 

[T. Valko]

I have this formula:

=IF(R3="Yes","DO NOT COUNT",
IF(OR(D3="Unknown",J3={"Terminated","Active - Kiosk"}),"DO NOT COUNT",
IF(J3="ACTIVE",CONCATENATE(M3,O3),
"Check Record")))

Which works fine, but I need to add one more item that refers to a table to identify "do not count" records. I tried to incorporate the following statement:

IF(VLOOKUP(H2,Pocket!$A$2:$B$213,2,FALSE)="Pocket","DO NOT COUNT",

to look like this:

=IF(R2="Yes","DO NOT COUNT",
IF(OR(D2="Unknown",J2={"Terminated","Active - Kiosk"}),"DO NOT COUNT",
IF(VLOOKUP(H2,Pocket!$A$2:$B$213,2,FALSE)="Pocket","DO NOT COUNT",
IF(J2="ACTIVE",CONCATENATE(M2,O2),
"Check Record"))))

The problem with this is that it returns #N/A because if value is not found on table then it returns the error....i tried different combinations but I just don't seem to come up with the right answer..... can anyone help with some ideas.....PLEASE!!

Try it like this...

...IF(COUNTIF(Pocket!$A$2:$A$213,H2),IF(VLOOKUP(H2,Pocket!$A$2:$B$213,2,0)="Pocket","DO NOT COUNT"))...

Instead of have separate tests with the result of "do not count" put all of those in the OR.

 

[HRIS]

I have this formula:

=IF(R3="Yes","DO NOT COUNT",
IF(OR(D3="Unknown",J3={"Terminated","Active - Kiosk"}),"DO NOT COUNT",
IF(J3="ACTIVE",CONCATENATE(M3,O3),
"Check Record")))

Which works fine, but I need to add one more item that refers to a table to identify "do not count" records. I tried to incorporate the following statement:

IF(VLOOKUP(H2,Pocket!$A$2:$B$213,2,FALSE)="Pocket","DO NOT COUNT",

to look like this:

=IF(R2="Yes","DO NOT COUNT",
IF(OR(D2="Unknown",J2={"Terminated","Active - Kiosk"}),"DO NOT COUNT",
IF(VLOOKUP(H2,Pocket!$A$2:$B$213,2,FALSE)="Pocket","DO NOT COUNT",
IF(J2="ACTIVE",CONCATENATE(M2,O2),
"Check Record"))))

The problem with this is that it returns #N/A because if value is not found on table then it returns the error....i tried different combinations but I just don't seem to come up with the right answer..... can anyone help with some ideas.....PLEASE!!

Your formula worked for me if I put the VLOOKUP formula as one of the following:
VLOOKUP(H2,Pocket,2,FALSE)
VLOOKUP(H2,$A$2:$B$213,2,FALSE)

It seems that it may be having an issue figuring where your table array is when listed as Pocket!$A$2:$B$213 ?

 

[octord]

Re: Multiple IF statements with nested vlookup function

First of all, thank you for your help... very much appreciated!!

I apologize for not being so clear on what I'm trying to accomplish so I'll try a better way.... I use the results of the formula to do summaries and graphs.

The goal is to identify useful records and eliminate the rest.... I identify the record as "Do Not Count" (as a marker) but has nothing to do with countif at this point until the summary picks up the information.

When I input:

=IF(OR(R3="Yes",D3="Unknown",J3={"Terminated","Active - Kiosk"}),"DO NOT COUNT",
IF(J3="ACTIVE",CONCATENATE(M3,O3),
"Check Record"))

It gives me the partial results I'm looking for at this point.... there is one more condition that I need to incorporate to complete the report so I though if add the following

IF(VLOOKUP(H2,Pocket!$A$2:$B$213,2,FALSE)="Pocket","DO NOT COUNT",

which is looking H2 value in the pocket table and if found then mark the record as "Do Not Count" so it will trigger the summary to not count it..... the problem is that if H2 value is not found in the pocket table then it should trigger the next condition which is

IF(J2="ACTIVE",CONCATENATE(M2,O2),

but instead it returns #N/A as a value which I now understand why....maybe using vlookup is not the best way to accomplish this so if I cannot figure out how to incorporate the last condition then I have no choice but to add another column to identify the pocket records and just add another condition to the OR statement.

If you have any suggestions I would appreciate your input.

Thanks,

-O

 

[T. Valko]

Re: Multiple IF statements with nested vlookup function

First of all, thank you for your help... very much appreciated!!

I apologize for not being so clear on what I'm trying to accomplish so I'll try a better way.... I use the results of the formula to do summaries and graphs.

The goal is to identify useful records and eliminate the rest.... I identify the record as "Do Not Count" (as a marker) but has nothing to do with countif at this point until the summary picks up the information.

When I input:

=IF(OR(R3="Yes",D3="Unknown",J3={"Terminated","Active - Kiosk"}),"DO NOT COUNT",
IF(J3="ACTIVE",CONCATENATE(M3,O3),
"Check Record"))

It gives me the partial results I'm looking for at this point.... there is one more condition that I need to incorporate to complete the report so I though if add the following

IF(VLOOKUP(H2,Pocket!$A$2:$B$213,2,FALSE)="Pocket","DO NOT COUNT",

which is looking H2 value in the pocket table and if found then mark the record as "Do Not Count" so it will trigger the summary to not count it..... the problem is that if H2 value is not found in the pocket table then it should trigger the next condition which is

IF(J2="ACTIVE",CONCATENATE(M2,O2),

but instead it returns #N/A as a value which I now understand why....maybe using vlookup is not the best way to accomplish this so if I cannot figure out how to incorporate the last condition then I have no choice but to add another column to identify the pocket records and just add another condition to the OR statement.

If you have any suggestions I would appreciate your input.

Thanks,

-O

The COUNTIF is making sure the lookup value H3 does exist. If COUNTIF returns a number >0 that means the lookup value exists then it executes the inner IF VLOOKUP. If COUNTIF returns 0 that means the lookup value does not exist then it bypasses the inner IF VLOOKUP preventing the #N/A error.

See if this does what you want. It's a real mouthful!

=IF(OR(R3="Yes",D3="Unknown",J3={"Terminated","Active - Kiosk"}),"DO NOT COUNT",IF(COUNTIF(Pocket!$A$2:$A$213,H3),IF(VLOOKUP(H3,Pocket!$A$2:$B$213,2,0)="Pocket","DO NOT COUNT"),IF(J3="ACTIVE",CONCATENATE(M3,O3),"Check Record")))

Make sure all the row references are correct. I used all references to row 3.

 

[octord]

Biff,

Thank you so very much! The answer was there from the beginning and I fail to notice that.... found myself totally focus on vlookup and just ignored the rest... I would have never thought of doing this and now thanks to you I have a new tool that I see great potential to make daily job much easier....THANKS!!

-O

 

[T. Valko]

You're welcome. Thanks for the feedback!