NESTED IF & VLOOKUP Formulas returning the correct data, but ...

[dante311]

Hi All -

Please find my "code" for a working IF(VLOOKUP... that does capture the data from the references table successfully. To start at the beginning, I have 5 value tables where data is being pulled from with a master table that is pulling the data from the 5 tables.

My issue is that cells that are empty in the data tables (from where data is pulled) is revealed as a "#N/A" in the master table and I want it to reveal a blank space...

=IF($A$2="Table 1",(VLOOKUP(MM,MM[Priority],1,0)),IF($A$2="Table 2",(VLOOKUP(RG,RG[Priority],1,0)),IF($A$2="Table 3",(VLOOKUP(CS,CS[Priority],1,0)),IF($A$2="Table 4",(VLOOKUP(JP,JP[Priority],1,0)),IF($A$2="Table 5",(VLOOKUP(JB,JB[Priority],1,0)),0)))))

I was hoping to return a 0 for false statements, which then I would create a new rule setting 0 = ;;; which returns a blank cell.

Any help?

Thank you in advance!

 

[Joe4]

The "#N/A" error usually happens when it cannot find a match. You can override that with the IFERROR function, in which you tell it what to return in place of an error.
That structure would look like:
=IFERROR(your formula," ")

 

[dante311]

The "#N/A" error usually happens when it cannot find a match. You can override that with the IFERROR function, in which you tell it what to return in place of an error.
That structure would look like:
=IFERROR(your formula," ")

Hey thanks for the prompt reply

The only issue with the iferror is that I’m being told I have too many arguments... is there a way around this?

 

[Joe4]

Just drop your ENTIRE formula in the "your formula" argument above, except for the equal sign.
If your original formula is correct (has no errors), it should work.

Note that IFERROR was introduced in Excel 2007. So as long as you are not using an older Excel version, it should work.
See: https://exceljet.net/excel-functions/excel-iferror-function

 

[dante311]

Just drop your ENTIRE formula in the "your formula" argument above, except for the equal sign.
If your original formula is correct (has no errors), it should work.

Note that IFERROR was introduced in Excel 2007. So as long as you are not using an older Excel version, it should work.
See: https://exceljet.net/excel-functions/excel-iferror-function

It's the most recent version of excel. Thanks again for your help.

So when I drop the entire formula into the above function it's telling me too few arguments. If I remove the FIRST IF( and add the IFERROR( in lieu of it... too many arguments.

I've tried making all IF( into IFERROR(... doesn't work. Give me a syntax error.

 

[Joe4]

Does the original formula work without error?
I cannot really check it, as I do not have the tables/sheets you reference.

If the formula you originally posted works, then this should work:

[COLOR=#333333][I]=IFERROR(IF($A$2="Table 1",(VLOOKUP(MM,MM[Priority],1,0)),IF($A$2="Table 2",(VLOOKUP(RG,RG[Priority],1,0)),IF($A$2="Table 3",(VLOOKUP(CS,CS[Priority],1,0)),IF($A$2="Table 4",(VLOOKUP(JP,JP[Priority],1,0)),IF($A$2="Table 5",(VLOOKUP(JB,JB[Priority],1,0)),0)))))," ")[/I][/COLOR]

 

[dante311]

Does the original formula work without error?
I cannot really check it, as I do not have the tables/sheets you reference.

If the formula you originally posted works, then this should work:

[COLOR=#333333][I]=IFERROR(IF($A$2="Table 1",(VLOOKUP(MM,MM[Priority],1,0)),IF($A$2="Table 2",(VLOOKUP(RG,RG[Priority],1,0)),IF($A$2="Table 3",(VLOOKUP(CS,CS[Priority],1,0)),IF($A$2="Table 4",(VLOOKUP(JP,JP[Priority],1,0)),IF($A$2="Table 5",(VLOOKUP(JB,JB[Priority],1,0)),0)))))," ")[/I][/COLOR]

I'll try this again. Unfortunately I can't share the tables. I should clarify by saying that each table is its own spreadsheet/tab referencing a different set of data values in a table. I've created unique tables that provide data pulled to the master table with this code referencing different tables, columns, etc...

strange that I can't turn the #N/A for a blank value in the referencing tables to a blank in the master table.

thanks for trying...

 

[Joe4]

Try the following:

Enter the formula originally posted to this thread, and make sure that it works (and there isn't a typo or anything).
So here is the formula you originally posted:

[COLOR=#333333][FONT=Tahoma][I]=IF($A$2="Table 1",(VLOOKUP(MM,MM[Priority],1,0)),IF($A$2="Table 2",(VLOOKUP(RG,RG[Priority],1,0)),IF($A$2="Table 3",(VLOOKUP(CS,CS[Priority],1,0)),IF($A$2="Table 4",(VLOOKUP(JP,JP[Priority],1,0)),IF($A$2="Table 5",(VLOOKUP(JB,JB[Priority],1,0)),0)))))[/I][/FONT][/COLOR]

If that doesn't work (if it tells you that you have a formula error) fix that first so that you have a working formula.
If it does work (and returns your #N/A value), then copy and paste and try the formula that I posted in my last post and see what that does.
Here is that formula here:

[COLOR=#333333]=IFERROR(IF($A$2="Table 1",(VLOOKUP(MM,MM[Priority],1,0)),IF($A$2="Table 2",(VLOOKUP(RG,RG[Priority],1,0)),IF($A$2="Table 3",(VLOOKUP(CS,CS[Priority],1,0)),IF($A$2="Table 4",(VLOOKUP(JP,JP[Priority],1,0)),IF($A$2="Table 5",(VLOOKUP(JB,JB[Priority],1,0)),0)))))," ")[/COLOR]

 

[dante311]

Try the following:

Enter the formula originally posted to this thread, and make sure that it works (and there isn't a typo or anything).
So here is the formula you originally posted:

[COLOR=#333333][FONT=Tahoma][I]=IF($A$2="Table 1",(VLOOKUP(MM,MM[Priority],1,0)),IF($A$2="Table 2",(VLOOKUP(RG,RG[Priority],1,0)),IF($A$2="Table 3",(VLOOKUP(CS,CS[Priority],1,0)),IF($A$2="Table 4",(VLOOKUP(JP,JP[Priority],1,0)),IF($A$2="Table 5",(VLOOKUP(JB,JB[Priority],1,0)),0)))))[/I][/FONT][/COLOR]

If that doesn't work (if it tells you that you have a formula error) fix that first so that you have a working formula.
If it does work (and returns your #N/A value), then copy and paste and try the formula that I posted in my last post and see what that does.
Here is that formula here:

[COLOR=#333333]=IFERROR(IF($A$2="Table 1",(VLOOKUP(MM,MM[Priority],1,0)),IF($A$2="Table 2",(VLOOKUP(RG,RG[Priority],1,0)),IF($A$2="Table 3",(VLOOKUP(CS,CS[Priority],1,0)),IF($A$2="Table 4",(VLOOKUP(JP,JP[Priority],1,0)),IF($A$2="Table 5",(VLOOKUP(JB,JB[Priority],1,0)),0)))))," ")[/COLOR]

wait... that second formula... I think it works.

You might be my hero.

 

[dante311]

wait... that second formula... I think it works.

You might be my hero.

It works - you saved the day! :D