Multiple IF statements if nested vlookup function

octord

New Member
Joined
Apr 12, 2010
Messages
16
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!!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Board Regular
Joined
Dec 29, 2010
Messages
116
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

New Member
Joined
Apr 12, 2010
Messages
16
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

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

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.
 
Last edited:

octord

New Member
Joined
Apr 12, 2010
Messages
16
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
 

Forum statistics

Threads
1,141,019
Messages
5,703,776
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top