Match/index/iserror

Hi all!

I've been working on this for a couple of days now and seem to be getting nowhere! Here is the issue:

I want the formula to search for a match within an array and return the corresponding number that also matches. It works fine as long as there is a match, but if no match is found it is supposed to just return a blank. Without ISERROR it returns #N/A when no match is found, with ISERROR it returns #REF when a match is found. Perplexing.

Can anyone assist? Worksheet attached with comments and details.

Thanks!

Originally Posted by arthurbr

Welcome to the board
I took the liberty of changing your formula combining COUNTIF and VLOOKUP.
Is ths what you need?

Thanks! I played with it and changed some reference ranges but it works! Simple, elegant. Love it!

Why make it more complicated than it has to be right?

Glad I found you guys!

You're welcome

I have three columns (actual, plan, variance)..the lookup works beautifully BTW. In the PLAN column I can have a value or not. How would I rewrite the following formula (which doesn't work without getting #VALUE):

=IF(F10>0,E10-F10,IF(F10="","",0))

The point being: if PLAN is blank then return nothing (blank), if PLAN is not blank then calculate and return result.

Did I not put enough arguments into this? I tried it with OR but that didn't work either.

I didn't really state the my problem very clearly, my mistake

Ok, the formula needs to to this:

IF cell E is blank AND cell F is blank, then return a blank...however,

IF cell is E blank AND cell F is not blank, then return a blank...however,

IF cell E is not blank AND cell F is not blank, then calculate.

I tried it with IF(AND.... a combination of If(AND..., OR(

and I just can't get it to return anything but #VALUE.

Originally Posted by gmcana

I didn't really state the my problem very clearly, my mistake

Ok, the formula needs to to this:

IF cell E is blank AND cell F is blank, then return a blank...however,

IF cell is E blank AND cell F is not blank, then return a blank...however,

IF cell E is not blank AND cell F is not blank, then calculate.

I tried it with IF(AND.... a combination of If(AND..., OR(

and I just can't get it to return anything but #VALUE.

The two first conditions boil down to checking if E is blank, whatever F, return a blank

so the formula would be

Please Login or Register  to view this content.

supposing when E is not blank but F isblank, the result would be blank