ISBLANK & IFERROR

[3G]

Hi there-

I have a user access form with some data validation that I'd like to have "blank" until a user selects some of the drop downs. However, my current formula is returning the result of the IFERROR condition as nothing has been selected. What I'd like to have happen is if F10 is blank, I want the rest of the columns to be blank. (F10 is the first required piece of information for them to enter). Users have 4 pieces of information to submit before the formula will evaluate to a result, or an error (The "No application exists" portion of the formula below). Those pieces of information are F10, H10, I10, & J10 which are concatenated into W10 and sent into the Match portion of the formula.

=IF(F10="","",IFERROR(INDEX(Settings!P$3:P$168,MATCH($W10,Settings!$O$3:$O$168,0),MATCH(M$9,Settings!$P$2:$W$2)),"No Application Exists"))

I also have just a plain old

=IFERROR(INDEX(Settings!P$3:P$168,MATCH($W10,Settings!$O$3:$O$168,0),MATCH(M$9,Settings!$P$2:$W$2)),"No Application Exists")

but again, it evaluates to the IFERROR portion because W10 is blank (it is a helper/feeder column to the index/match)

Any ideas?

 

[shrivallabha]

Your first formula works for me as it is and gives me expected blank.

Can you confirm if your cell F10 is absolutely blank i.e. it doesn't contain any white space character which is invisible to eyes?

 

[NARAYANK991]

Hi ,

I am not very clear on your problem.

If you say that W10 is the concatenated result of concatenating F10, H10, I10, & J10 , and if you want that all of the 4 cells should have data in them before the INDEX function is processed , then why not use W10 instead of F10 in the IF function , as in :

=IF(W10="","",IFERROR(INDEX(Settings!P$3:P$168,MATCH($W10,Settings!$O$3:$O$168,0),MATCH(M$9,Settings!$P$2:$W$2)),"No Application Exists"))

Narayan

 

[3G]

Hi Narayan-
I tried that, but it was still evaluating the IFERROR portion because W10 was not finding a match in the INDEX/MATCH. However, you did give me an idea for the following:

'=IF(OR(F10="",H10="",I10="",J10="",W10=""),"",IFERROR(INDEX(Settings!P$3:P$168,MATCH($W10,Settings!$O$3:$O$168,0),MATCH(M$9,Settings!$P$2:$W$2)),"No Application Exists"))

which seems to be working fine now...

thanks for the input!

 

[shrivallabha]

If W10 is mere concatenation of first 4 columns then you can also use:

=IF(COUNTA(F10,H10,I10,J10)=4,IFERROR(INDEX(Settings!P$3:P$168,MATCH($W10,Settings!$O$3:$O$168,0),MATCH(M$9,Settings!$P$2:$W$2)),"No Application Exists"),"")

 

[3G]

OOO! I like that Shrivallabha!! Thanks!