How to mix an IFNA with a IFVLOOKUP

Hi folks,

I'm trying to make the below formula work and keep coming back to the same error message - You've entered to many arguments for this function.

=IFNA(VLOOKUP(E:E,Sheet2!A:A,1,0),"No",IF(VLOOKUP(E:E,Sheet2!A:A,1,0)>=Sheet2!H2,"Correct","incorrect"))

I'm assuming there's a logic rule that means i can't mix these three functions together in this way?

Any assistance would be greatly appreciated.

Many thanks,

Weasel.

Hi Weasel

You are asking two independent question in this formula

The first is

to vlookup and if the value is N/A to return "NO"

The is one complete line and the end of the argument(s) However already in this you have a range for a single criteria

you cannot lookup E:E as this is an entire column. Maybe you need to lookup E2?

However you then go on to ask for another return which is where the too many arguments comes in.

What I think you are trying to achieve would require you to write this

=IFNA(IF(VLOOKUP(E2,Sheet2!A:A,1,0)>=Sheet2!H2,"Correct","incorrect"),"No")

Furthermore, you may be better using match as opposed to vlookup.

If you could upload a sample file I can certainly help with more conviction

Not sure what you are trying to do: normally with VLOOKUP you have a single cell as first parameter e.g VLOOKUP(E2,Sheet2!A;A,0)

The two VLOOKUP formulae are the same: do you mean this ?

=IFERROR(IF(VLOOKUP(E2,Sheet2!A2:A1000,1,0)>=Sheet2!H2,"Correct","incorrect"),"No")

so "No" is returned if there is an "Error" condition.


Note also it not sensible to use whole column ranges (A:A) but restrict them to you likely maximum.

Huge thanks to both of you for this - good to know going forward

Ok additional question - what if i wanted to add another variable to teh formula.

What if the cell the Vlookup starts with is blank, and i want the answer to say something else? how would i go about adding this into the formula - i've tried variations of adding an additional IF etc... but with no luck

Any help always appreciated

Which cell .. A2 or E2???

Sorry - should have specified - E2.

Thanks

=IF(E2="","SAY WHAT YOU WANT HERE",IFERROR(IF(VLOOKUP(E2,Sheet2!A2:A1000,1,0)>=Sheet2!H2,"Correct","incorrect"),"No"))

OR

=IF(ISBLANK(E2),"SAY WHAT YOU WANT HERE",IFERROR(IF(VLOOKUP(E2,Sheet2!A2:A1000,1,0)>=Sheet2!H2,"Correct","incorrect"),"No"))

My personal preference is the first option

Sorry - should have specified - E2.

Thanks

Once again, thanks for the excellent help

No problem, glad I could be of some use.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.