Index match column and row

[SAXON10]

Hi,

I am trying to index match with three different table ranges. I am apply the following formula in

B16=IF(MATCH(A$15,$A$2:$B$2,0),VLOOKUP($A16,$A$3:$B$12,2,0)) the formula working fine but the problem is every time I have change table ranges. I

need one formula for whole table range.

Any help much appreciated.

Data Range: A2:K12

DATA
TATA	TEXT		FORCE	TEXT	TEXT1		HERO	TEXT	TEXT1	TEXT2
5848	ORDER		5848	ORDER	US		5848	ORDER	US	US
6847	NOT		6847	NOT	UK		6847	ORDER	UK	UK
68798	ORDER		68798	ORDER	CHINA		68798	ORDER	CHINA	CHINA
4587	ORDER		4587	ORDER	NZ		4587	ORDER	NZ	NZ
1112	ORDER		1112	NOT	AUS		1112	ORDER	AUS	AUS
22235	ORDER		22235	NOT	EUR		22235	ORDER	EUR	EUR
456	ORDER		456	CANCEL	RUS		456	ORDER	RUS	RUS
487	ORDER		487	ORDER	ERA		487	REJ	ERA	ERA
587	NOT		587	NOT	NIPA		587	PENDING	NIPA	NIPA
657	ORDER		657	ORDER	BAN		657	ORDER	BAN	BAN
FORMULA REQUIRED
TATA	TEXT	FORCE	TEXT	HERO	TEXT
6847	NOT	1112	NOT	456	ORDER
68798	ORDER	22235	NOT	487	REJ
4587	ORDER	456	CANCEL	587	PENDING
1112	ORDER

<tbody>
</tbody>

https://www.dropbox.com/s/379m87ppijo31b2/INDEX MATCH.xlsx?dl=0

 

[VBA Geek]

in B16:
<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B16</th><td style="text-align:left">=INDEX(<font color="Blue">(<font color="Red">$A$2:$B$11,$D$2:$F$11,$H$2:$K$11</font>),MATCH(<font color="Red">A16,$A$2:$A$11,0</font>),2,1+QUOTIENT(<font color="Red">COLUMNS(<font color="Green">$B16:B16</font>)-1,2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

then copy it into the other green cells

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	TATA	TEXT		FORCE	TEXT	TEXT1		HERO	TEXT	TEXT1	TEXT2
2	5848	ORDER		5848	ORDER	US		5848	ORDER	US	US
3	6847	NOT		6847	NOT	UK		6847	ORDER	UK	UK
4	68798	ORDER		68798	ORDER	CHINA		68798	ORDER	CHINA	CHINA
5	4587	ORDER		4587	ORDER	NZ		4587	ORDER	NZ	NZ
6	1112	ORDER		1112	NOT	AUS		1112	ORDER	AUS	AUS
7	22235	ORDER		22235	NOT	EUR		22235	ORDER	EUR	EUR
8	456	ORDER		456	CANCEL	RUS		456	ORDER	RUS	RUS
9	487	ORDER		487	ORDER	ERA		487	REJ	ERA	ERA
10	587	NOT		587	NOT	NIPA		587	PENDING	NIPA	NIPA
11	657	ORDER		657	ORDER	BAN		657	ORDER	BAN	BAN
12
13
14
15	TATA	TEXT	FORCE	TEXT	HERO	TEXT
16	6847	NOT	1112	NOT	456	ORDER
17	68798	ORDER	22235	NOT	487	REJ
18	4587	ORDER	456	CANCEL	587	PENDING
19	1112	ORDER
Sheet4

 

[SAXON10]

VBA Geek ; Thank you so much for your help. formula working fine. You are legend.

 

[SAXON10]

Quick question how do add the name range(A15) in the same formula. Please help me.

in B16:
Worksheet Formulas

Cell	Formula
B16	=INDEX(($A$2:$B$11,$D$2:$F$11,$H$2:$K$11),MATCH(A16,$A$2:$A$11,0),2,1+QUOTIENT(COLUMNS($B16:B16)-1,2))

<thead>
</thead><tbody>
</tbody>

then copy it into the other green cells


Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K
1	TATA	TEXT		FORCE	TEXT	TEXT1		HERO	TEXT	TEXT1	TEXT2
2	5848	ORDER		5848	ORDER	US		5848	ORDER	US	US
3	6847	NOT		6847	NOT	UK		6847	ORDER	UK	UK
4	68798	ORDER		68798	ORDER	CHINA		68798	ORDER	CHINA	CHINA
5	4587	ORDER		4587	ORDER	NZ		4587	ORDER	NZ	NZ
6	1112	ORDER		1112	NOT	AUS		1112	ORDER	AUS	AUS
7	22235	ORDER		22235	NOT	EUR		22235	ORDER	EUR	EUR
8	456	ORDER		456	CANCEL	RUS		456	ORDER	RUS	RUS
9	487	ORDER		487	ORDER	ERA		487	REJ	ERA	ERA
10	587	NOT		587	NOT	NIPA		587	PENDING	NIPA	NIPA
11	657	ORDER		657	ORDER	BAN		657	ORDER	BAN	BAN
12
13
14
15	TATA	TEXT	FORCE	TEXT	HERO	TEXT
16	6847	NOT	1112	NOT	456	ORDER
17	68798	ORDER	22235	NOT	487	REJ
18	4587	ORDER	456	CANCEL	587	PENDING
19	1112	ORDER

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

 

[VBA Geek]

hi, what do you mean the name range A15? I am missing something

 

[SAXON10]

Hi,

Yes you missing the following name range TATA, HERO and FORCE.(MATCH(FORCE)(A15,C15 AND E15)