# Excel If Function With More Condition with Match Function

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.

• 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

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

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
• 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
• Ah... i found it... Thanks for the Solution... it Really Help Us So Much... Nov 26, 2015 at 1:28