Index & Match With Multiple Columns

[drcolinb]

I would like to use index and match with multiple columns. For example, using the data below, I want to index column B by searching for the values in column D in the array F1:H12.

So, index(B1:B12, match(D1, F1:H12, 0)) would return April.

I realise that match() does not work with multiple columns. Is there some way of modifying the function to deal with this issue?

Any help would be greatly appreciated!

[TABLE="width: 1174"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"] I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]January[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4001-01[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4101-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4102-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4101-02[/TD]
[TD]=index(B1:B12, match(D1, F1:H12, 0)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]February[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4002-01[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4201-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4202-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4201-02[/TD]
[TD]=index(B1:B12, match(D2, F1:H12, 0)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]March[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4003-01[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4301-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4302-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4301-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]April[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4004-01[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4001-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4002-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4001-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]May[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4005-01[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4003-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4004-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4003-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]June[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4101-02[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4005-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4102-02[/TD]
[TD]CLB-FC-2D-ST-SPA-4005-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]July[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4007-01[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4006-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4202-02[/TD]
[TD]CLB-FC-2D-ST-SPA-4006-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]August[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4008-01[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4007-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4302-02[/TD]
[TD]CLB-FC-2D-ST-SPA-4007-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]September[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4011-02[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4008-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4002-02[/TD]
[TD]CLB-FC-2D-ST-SPA-4008-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]October[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4010-01[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4009-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4004-02[/TD]
[TD]CLB-FC-2D-ST-SPA-4009-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]November[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4001-02[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4010-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4005-03[/TD]
[TD]CLB-FC-2D-ST-SPA-4010-02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]December[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4102-01[/TD]
[TD][/TD]
[TD]CLB-FC-2D-ST-SPA-4011-01[/TD]
[TD]CLB-FC-2D-ST-SPA-4006-03[/TD]
[TD]CLB-FC-2D-ST-SPA-4011-02[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sanrv1f]

Welcome to the forum

try this array formula, confirm with Ctrl+Shift+Enter

=INDEX($B$2:$B$13,SMALL(IF($F$2:$H$13=D2,ROW($F$2:$H$13)-ROW($F$2)+1),1))

 

[gaz_chops]

Try
=INDEX(A1:A12,IFERROR(MATCH(B1,C1:C12,0),IFERROR(MATCH(B1,D1:D12,0),IFERROR(MATCH(B1,E1:E12,0),""))))

 

[Snakehips]

drcolinb,

In respect of your posted data set try...

Excel 2007
	B	C	D	E	F	G	H	I	J
1	January		CLB-FC-2D-ST-SPA-4001-01		CLB-FC-2D-ST-SPA-4101-01	CLB-FC-2D-ST-SPA-4102-01	CLB-FC-2D-ST-SPA-4101-02		April
2	February		CLB-FC-2D-ST-SPA-4002-01		CLB-FC-2D-ST-SPA-4201-01	CLB-FC-2D-ST-SPA-4202-01	CLB-FC-2D-ST-SPA-4201-02		April
3	March		CLB-FC-2D-ST-SPA-4003-01		CLB-FC-2D-ST-SPA-4301-01	CLB-FC-2D-ST-SPA-4302-01	CLB-FC-2D-ST-SPA-4301-02		May
4	April		CLB-FC-2D-ST-SPA-4004-01		CLB-FC-2D-ST-SPA-4001-01	CLB-FC-2D-ST-SPA-4002-01	CLB-FC-2D-ST-SPA-4001-02		May
5	May		CLB-FC-2D-ST-SPA-4005-01		CLB-FC-2D-ST-SPA-4003-01	CLB-FC-2D-ST-SPA-4004-01	CLB-FC-2D-ST-SPA-4003-02		June
6	June		CLB-FC-2D-ST-SPA-4101-02		CLB-FC-2D-ST-SPA-4005-01	CLB-FC-2D-ST-SPA-4102-02	CLB-FC-2D-ST-SPA-4005-02		January
7	July		CLB-FC-2D-ST-SPA-4007-01		CLB-FC-2D-ST-SPA-4006-01	CLB-FC-2D-ST-SPA-4202-02	CLB-FC-2D-ST-SPA-4006-02		August
8	August		CLB-FC-2D-ST-SPA-4008-01		CLB-FC-2D-ST-SPA-4007-01	CLB-FC-2D-ST-SPA-4302-02	CLB-FC-2D-ST-SPA-4007-02		September
9	September		CLB-FC-2D-ST-SPA-4011-02		CLB-FC-2D-ST-SPA-4008-01	CLB-FC-2D-ST-SPA-4002-02	CLB-FC-2D-ST-SPA-4008-02		December
10	October		CLB-FC-2D-ST-SPA-4010-01		CLB-FC-2D-ST-SPA-4009-01	CLB-FC-2D-ST-SPA-4004-02	CLB-FC-2D-ST-SPA-4009-02		November
11	November		CLB-FC-2D-ST-SPA-4001-02		CLB-FC-2D-ST-SPA-4010-01	CLB-FC-2D-ST-SPA-4005-03	CLB-FC-2D-ST-SPA-4010-02		April
12	December		CLB-FC-2D-ST-SPA-4102-01		CLB-FC-2D-ST-SPA-4011-01	CLB-FC-2D-ST-SPA-4006-03	CLB-FC-2D-ST-SPA-4011-02		January
Sheet2

Cell Formulas
Range	Formula
J1	{=INDEX($B$1:$B$12,MAX(($F$1:$H$12=D1)*ROW($F$1:$H$12)),)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.

 

[drcolinb]

Thank you! That is exactly what I needed.

In reality, there are many columns to search, so both of the array formulas work great