0

i had a problem with if function with more condition and match function. May i had Solution?

Currently I try using this array formula but cannot get any result.

{=IF(MATCH(A1;'6(AKK)'!$A$1:$A$100);"AKK";

IF(MATCH(A1;'6(K3)'!$A$1:$A$100);"K3";

IF(MATCH(A1;'6(Kesling)'!$A$1:$A$100);"Kesehatan Lingkungan";

IF(MATCH(A1;'6(Kespro)'!$A$1:$A$100);"Kesehatan Reproduksi";

IF(MATCH(A1;'6(Promkes)'!$A$1:$A$100);"Promosi Kesehatan";

IF(MATCH(A1;'6(Epid)'!$A$1:$A$100);"Epidemiologi";

IF(MATCH(A1;'6(Gizi)'!$A$1:$A$100);"Gizi Kesehatan Masyarakat";"Gagal")))))))}

For example, data in Main Sheet looks like below.

      A                B
  1. R.15.01.001 (This is Cell Formula)

  2. R.15.01.002

  3. R.15.01.003

For Example, data in Sheet "6(AKK)"

      A

1 R.15.01.001

2 R.15.01.003

3 R.15.01.007

4 etc...

For Example, data in Sheet "6(K3)"

      A

1 R.15.01.002

2 R.15.01.004

3 R.15.01.005

4 etc...

Every Sheet looks like that without same number in every sheet 6. i need it for some Task. and i Hope i had a solution Here.

1
  • What is the purpose of this formula? Are you trying to return which sheet the value is found on?
    – Kyle
    Nov 25, 2015 at 14:00

1 Answer 1

0

Tere are two ways to accomplish this.

Option 1 (clunky and less sophisticated, but perhaps easier to follow)

Because the MATCH function returns #N/A (or an error) when it can't find the match against a specific sheet, one solution is to wrap ISNA statements into your nested IF statements.

I have given you an example for the first 3 sheets here:

=IF(ISNA(MATCH(A2,'6(AKK)'!$A$1:$A$100,0)),IF(ISNA(MATCH(A2,'6(K3)'!$A$1:$A$100,0)),IF(ISNA(MATCH(A1,'6(KESLING)'!$A$1:$A$100)),"[See Note]","Kesehatan Lingkungan"),"K3"),"AKK")

Note - keep embedding the IF(ISNA(MATCH ... statements here. On the last one, place "Gagal" in this spot.

As you can see, it will get pretty long and ugly. Yet it will work.

Lastly, enter this a regular formula, not an array formula.

Option 2 (more sophisticated and efficient, requires a little more initial input)

  1. Define a Named Range as sList with the formula =MID(TRANSPOSE(GET.WORKBOOK(1))&T(NOW()),FIND("]",TRANSPOSE(GET.WORKBOOK(1))&T(NOW()))+1,255) (see more about GET.WORKBOOK)
  2. Set up a reference table that has the sheet names with the corresponding return values and Define this Range with a name of return_values

enter image description here

  1. Enter this formula (as an array) into cell B1 =VLOOKUP(INDEX(sList,MATCH(TRUE,COUNTIF(INDIRECT("'"&sList&"'!$A$1:$A$100"),A1)>0)),return_value,2,0) and drag down the rows for each value in column A
2
  • Yet it really Worked. But i have a problem for place "Gagal" in that spot. where i can place "Gagal"? =IF(ISNA(MATCH(C11;'6(AKK)'!$C$6:$C$105;0));IF(ISNA(MATCH(C11;'6(K3)'!$C$6:$C$105;0));IF(ISNA(MATCH(C11;'6(Kesling)'!$C$6:$C$105;0));IF(ISNA(MATCH(C11;'6(Kespro)'!$C$6:$C$105;0));IF(ISNA(MATCH(C11;'6(Promkes)'!$C$6:$C$105;0));IF(ISNA(MATCH(C11;'6(Epid)'!$C$6:$C$105;0));IF(ISNA(MATCH(C11;'6(Gizi)'!$C$6:$C$105;0));"Gizi Kesehatan Masyarakat");"Epidemiologi");"Promosi Kesehatan");"Kesehatan Reproduksi");"Kesehatan Lingkungan");"K3");"AKK") Because the last one on Gizi false. Nov 26, 2015 at 1:22
  • 1
    Ah... i found it... Thanks for the Solution... it Really Help Us So Much... Nov 26, 2015 at 1:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .