Use IFERROR in VLOOKUP with IF statement present

[rdoulaghsingh]

Good evening. I'm using the formula below which works great, but when I drag it down to cells with no reference I get #N/A error.

=IF(VLOOKUP(A1295,'SP 800-53B SecBaseline R5'!$A:$G,{3,4,5,6,7},0)=0,"",VLOOKUP(A1295,'SP 800-53B SecBaseline R5'!$A:$G,{3,4,5,6,7},0))

I've modified the code to the following below using IFERROR, but I keep receiving the error "you've entered too many arguments for this function".

=IF(IFERROR(VLOOKUP(A1297,'SP 800-53B SecBaseline R5'!$A:$G,{3,4,5,6,7},0)=0,"",VLOOKUP(A1297,'SP 800-53B SecBaseline R5'!$A:$G,{3,4,5,6,7},0)),"")

What am I doing wrong?

 

[AhoyNC]

Try:

=IFERROR(IF(VLOOKUP(A1295,'SP 800-53B SecBaseline R5'!$A:$G,{3,4,5,6,7},0)=0,"",VLOOKUP(A1295,'SP 800-53B SecBaseline R5'!$A:$G,{3,4,5,6,7},0)),"")

 

[Peter_SSs]

Are you actually using anything like 1,000,000+ rows? If not, I would recommend avoiding whole column references.

What about something like this as an alternative (amend ranges if needed)

=LET(f,INDEX(FILTER('SP 800-53B SecBaseline R5'!C$1:G$2000,'SP 800-53B SecBaseline R5'!A$1:A$2000=A1295,""),1,0),IF(f=0,"",f))

 

[rdoulaghsingh]

Try:

=IFERROR(IF(VLOOKUP(A1295,'SP 800-53B SecBaseline R5'!$A:$G,{3,4,5,6,7},0)=0,"",VLOOKUP(A1295,'SP 800-53B SecBaseline R5'!$A:$G,{3,4,5,6,7},0)),"")

Wow! All I had to do was flip the IFERROR to the beginning? LOL. Thanks man!

 

[Peter_SSs]

Did you try the other suggestion too?

 

[rdoulaghsingh]

Did you try the other suggestion too?

I haven't tried it yet. The formula @AhoyNC presented was easier for me to understand based on the formula that I have now. Do you mind explaining the parts of this formula and how it works? I've never "LET" used in Excel formula before.

 

[AhoyNC]

Glad that worked for you, but I have to agree with Peter_SSs it's best not to use whole columns as a reference. Even if there is no data in the row the IFERROR function will look at all million plus rows which can slow your workbook down.

 

[Peter_SSs]

I've never "LET" used in Excel formula before.

LET allows you to effectively introduce variables into formulas. This can save calculation resources by not repeatedly calculating the same thing multiple times.

Take this simple example.
Column A contains a traditional formula and in it the 'ROWS' calculation is performed 3 times in every cell.
Column B produces the same result but the 'ROWS' calculation is only done once per cell and stored under the name 'rws'. That name is then used repeatedly in the formula instead but does not have to be recalculated each time. More information here: LET function

21 09 14.xlsm
	A	B
1	2	2
2	4	4
3	6	6
4	8	8
5	10	10
6	12	12
7	14	14
8	16	16
9	18	18
10	20	20
11	44	44
12	48	48
13	52	52
14	56	56
15	60	60
16	64	64
17	68	68
18	72	72
19	76	76
LET

Cell Formulas
Range	Formula
A1:A19	A1	=IF(ROWS(A$1:A1)>10,ROWS(A$1:A1)*4,ROWS(A$1:A1)*2)
B1:B19	B1	=LET(rws,ROWS(A$1:A1),IF(rws>10,rws*4,rws*2))

Do you mind explaining the parts of this formula and how it works?

I am happy to try to do that if the formula does what you want, so let's check that first. Not much point explaining if it isn't working for you.