Use IFERROR in VLOOKUP with IF statement present

rdoulaghsingh

Board Regular
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.

VBA Code:
``=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".

Code:
``=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?

Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

AhoyNC

Well-known Member
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

MrExcel MVP, Moderator
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)
Excel Formula:
``=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))``

Last edited:

rdoulaghsingh

Board Regular
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

MrExcel MVP, Moderator

Did you try the other suggestion too?

rdoulaghsingh

Board Regular
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

Well-known Member
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

MrExcel MVP, Moderator
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
AB
122
244
366
488
51010
61212
71414
81616
91818
102020
114444
124848
135252
145656
156060
166464
176868
187272
197676
LET
Cell Formulas
RangeFormula
A1:A19A1=IF(ROWS(A\$1:A1)>10,ROWS(A\$1:A1)*4,ROWS(A\$1:A1)*2)
B1:B19B1=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.

Replies
4
Views
61
Replies
10
Views
130
Replies
3
Views
374
Replies
22
Views
178
Replies
2
Views
227

1,147,731
Messages
5,742,852
Members
423,758
Latest member

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.

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

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