INDEX MATCH Function

**timbellamy** · 07-15-2010, 09:33 AM

Hi,

I have attached a sample file to explain my query better.

I col A i have a list of names and in Cells E2:L4 i have a table which tells me what each persons option is.

I would like Col B to automatically let me know which Option they have chosen.

Col C shows what the values should be.

I have tried INDEX and Match but it doesn't seem to be working - maybe something simple but any help wouldf be gratefully aprreciated.

Many thanks

**DonkeyOte** · 07-15-2010, 10:17 AM

A few options, one might be:

B2:
=IF(COUNTIF($E$3:$L$4,$A2),INDEX($E$2:$L$2,MIN(IF($E$3:$L$4=$A2,COLUMN($E$2:$L$2)-COLUMN($E$2)+1))),"")
confirmed with CTRL + SHIFT + ENTER
copied down

Note: Array entry ... confirming with Enter alone will not suffice.

If you do have only two rows of data:

B2:
=INDEX($E$2:$L$2,MATCH($A2,INDEX($E$3:$L$4,1+(COUNTIF($E$4:$L$4,$A2)>0),0),0))
confirmed with Enter
copied down

**timbellamy** · 07-15-2010, 10:57 AM

That works great thank you. I have actually got 10 rows of data but have taken the 1st one and amended it.

Just for future reference will the 2nd formula only ever work with 2 rows or could this be amended to work with 10 (or more rows). The people who will use the worksheet eventually may not remember to SHIFT+CTRL+ENTER.

Many thanks

**DonkeyOte** · 07-15-2010, 11:07 AM

The 2nd suggestion is not viable for 10 rows of data, no.

Conducting searches within Matrices is rarely efficient I'm afraid.

To circumvent the need for CSE entry you can embed an INDEX:

B2:
=IF(COUNTIF($E$3:$L$4,$A2),INDEX($E$2:$L$2,-MIN(INDEX(-(($E$3:$L$4=$A2)*(COLUMN($E$3:$L$3)-COLUMN($E$3)+1)),0))),"")
confirmed with Enter
copied down

However, the above will be no more efficient than the Array indeed with large datasets it would most likely prove slower.