Indirect references in Array formulae

[ninsine]

This array formula in cell B2 retruns the first match on a string (in A2)from a list of keywords - "ListA" (named range ($F2$F100))

{=IFERROR(INDEX(LISTA,MATCH(1,COUNTIF(A2,"*"&LISTA&"*"),0)),"")}

I want to use the result that I get from above formula as a named range to look up the corresponding row and get the first match that list on the

{=IFERROR(INDEX(SUM(INDIRECT(B2)),MATCH(1,COUNTIF($A2,"*"&SUM(INDIRECT(B2))&"*"),0)),"")}

For Eg:
If B2 gives me a result "Fruits", I want to use it as a named range (which I have already defined - Fruits: $G$2:$J$2) to get the first match on cell A2.

Keyword range
F2:F100 - LIST A
G2:J100 - Corresponding data based on ListA in each row Eg: F2 = Fruits G2:J2 = Apple, Orange, Grapes, Plum

But the second formula does not give any any result. Is there a workaround for this?

Appreciate your help
Note: I cannot upload files from here

[XOR LX]

Hi,

Haven't looked in great detail, but my first question would be to ask you why you have the SUM portion of your second formula; passing this to INDEX I suspect will not give you the desired result. Perhaps it should be:

{=IFERROR(INDEX(INDIRECT(B2),MATCH(1,COUNTIF($A2,"*"&INDIRECT(B2)&"*"),0)),"")}

Regards

[ninsine]

I had tried that. But was getting error message that "Excel ran out of resources.......". I added it so that excel wont update the range when columns are inserted.

[XOR LX]

?? It's INDIRECT that will prevent Excel from updating range references when e.g. columns are inserted, not SUM!

[ninsine]

My bad..I was thinking otherwise.