Adding extra if statements

**ChalkerL** · 11-21-2009, 03:04 PM

Hi, I'm wondering if it is possible to include addition if statement only when more than 5 results are returned.

Currently I'm doing a multiple if statement , 10 results are returned from the formula, and i would like to add extra if statement to filter only the top 6 results.

You can see the formula in shortlisted Admin - B10

These are the additional if statements :

IF(FIND(jobsApplicationdb!M2,'Person Specification'!$M$2),IF(FIND(jobsApplicationdb!N2,'Person Specification'!$M$2)

=IF(jobsApplicationdb!G2='Person Specification'!$F$2,IF(FIND(jobsApplicationdb!I2,'Person Specification'!$H$2),IF(FIND(jobsApplicationdb!H2,'Person Specification'!$G$2),IF(ISTEXT(jobsApplicationdb!J2),IF(AND(jobsApplicationdb!P2='Person Specification'!$N$2,jobsApplicationdb!Q2='Person Specification'!$O$2,jobsApplicationdb!R2='Person Specification'!$P$2,jobsApplicationdb!S2='Person Specification'!$Q$2,jobsApplicationdb!T2='Person Specification'!$R$2,jobsApplicationdb!M2>='Person Specification'!$L$2,jobsApplicationdb!AJ2="Yes",jobsApplicationdb!U2>='Person Specification'!$S$2,jobsApplicationdb!W2>='Person Specification'!$U$2,jobsApplicationdb!Y2>='Person Specification'!$W$2,jobsApplicationdb!AA2>='Person Specification'!$Y$2,jobsApplicationdb!AC2>='Person Specification'!$AA$2,jobsApplicationdb!AE2>='Person Specification'!$AC$2,jobsApplicationdb!AG2>='Person Specification'!$AE$2),jobsApplicationdb!C2,"")))))

Thanks

**Richard Buttrey** · 11-21-2009, 03:45 PM

Hi,

It's not clear entirely what the purpose of this big IF statement is. It just seems to be returning the NRIC number. Why not just list the names in column C of the Shorlisted.. sheet rather than a VLOOKUP and then useSurely you can just use

=INDEX(jobsApplicationdb!C:C,MATCH(C10,jobsApplicationdb!D:D,FALSE),1)

in B10 and copied down.

Incidentally there seems to be an inconsistency between the current B10 & B11 cells. B10 uses Row 2 whilst B11 uses row 4 rather than row 3.

However if you really need IF statements, (and they are getting rather unwieldy and hence difficult to read), can I suggest a better way would be to create a helper column in your Person Specification sheet. In this helper column concatenate all the columns that you are currently testing for in the big IF statement. i.e.

=F2&H2&G2&N2&R2 ......AE2

Then you can just use one IF statement and look at the helper column.

HTH

**ChalkerL** · 11-21-2009, 04:27 PM

Thanks for the suggestions.

The big if statement returns the nric, based on the comparison of two worksheets - jobsApplicationdb and Job Specification, and i'm checking if values in cells for both worksheets match, then return the nric.

You suggested using

=INDEX(jobsApplicationdb!C:C,MATCH(C10,jobsApplicationdb!D:D,FALSE),1)

Is it possible to do, if I'm using to worksheets?

=F2&H2&G2&N2&R2 ......AE2

In some of the if statements i had find, if i were to concatenate them, i might have trouble comparing.

Incidentally there seems to be an inconsistency between the current B10 & B11 cells. B10 uses Row 2 whilst B11 uses row 4 rather than row 3.

I actually hide the error in the nric column. Cause in the jobsApplicationdb the 2nd value is actually "KTP associate", but what i'm looking for is "Administrator" , so it displays only those with value and not those error.

Is it possible to add addition if statement when i can more than 10 results?