Index with match specific ranges

[SAXON10]

Hi,

I am trying to index match the specify type result. Column B4:B18 and C4:C18 contain code and text. Column C had a multiple types are

CRI,VRI and RRM.

I am looking for specific type (CRI) only.Column E4 match type and F4 code, If matched formula return the text, if not matched formula return

NA.

https://www.dropbox.com/s/4vak7dkkoa2oi09/INDEX.PNG?dl=0

Please help me.

[TABLE="width: 426"]
<tbody>[TR]
[TD="colspan: 2"]DATA[/TD]
[TD][/TD]
[TD="colspan: 3"]DESIRED RESULT[/TD]
[/TR]
[TR]
[TD]CODE[/TD]
[TD]TEXT[/TD]
[TD][/TD]
[TD]TYPE[/TD]
[TD]CODE[/TD]
[TD]TEXT[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]CRI[/TD]
[TD][/TD]
[TD]CRI[/TD]
[TD]2846-RRT[/TD]
[TD]CRI[/TD]
[/TR]
[TR]
[TD]111-AVT[/TD]
[TD]RRM[/TD]
[TD][/TD]
[TD][/TD]
[TD]4856-RRT[/TD]
[TD]CRI[/TD]
[/TR]
[TR]
[TD]111-AVT[/TD]
[TD]VRI[/TD]
[TD][/TD]
[TD][/TD]
[TD]4546-RRT[/TD]
[TD]CRI[/TD]
[/TR]
[TR]
[TD]1178-FT[/TD]
[TD]CRI[/TD]
[TD][/TD]
[TD][/TD]
[TD]111-AVT[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]1178-FT[/TD]
[TD]CRI[/TD]
[TD][/TD]
[TD][/TD]
[TD]4859-FFY[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]2846-RRT[/TD]
[TD]CRI[/TD]
[TD][/TD]
[TD][/TD]
[TD]7879-RRT[/TD]
[TD]CRI[/TD]
[/TR]
[TR]
[TD]2846-RRT[/TD]
[TD]VRI[/TD]
[TD][/TD]
[TD][/TD]
[TD]1178-FT[/TD]
[TD]CRI[/TD]
[/TR]
[TR]
[TD]4546-RRT[/TD]
[TD]RRM[/TD]
[TD][/TD]
[TD][/TD]
[TD]222[/TD]
[TD]CRI[/TD]
[/TR]
[TR]
[TD]4546-RRT[/TD]
[TD]CRI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4856-RRT[/TD]
[TD]VRI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4856-RRT[/TD]
[TD]CRI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4859-FFY[/TD]
[TD]VRI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4859-FFY[/TD]
[TD]RRM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7879-RRT[/TD]
[TD]CRI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7879-RRT[/TD]
[TD]RRM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

Would filtering on the column do what you want?

 

[Saba Sabaratnam]

Hi

You can use filter as suggested by MikeRickson.

Another way to do it is to use a formula.

Enter the following array formula in G4 by pressing Shift + Control + Enter and copy it down.

=IFERROR(INDEX($C$3:$C$18,MATCH(INDEX(IF($E$4=$C$3:$C$18,$B$3:$B$18,""),MATCH(F4,IF($E$4=$C$3:$C$18,$B$3:$B$18,""),0)),$B$3:$B$18,0)),"NA")

Kind regards

Saba

 

[AhoyNC]

Maybe:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G
3		CODE	TEXT		TYPE	CODE	TEXT
4		222	CRI		CRI	2846-RRT	CRI
5		111-AVT	RRM			4856-RRT	CRI
6		111-AVT	VRI			4546-RRT	CRI
7		1178-FT	CRI			111-AVT	NA
8		1178-FT	CRI			4859-FFY	NA
9		2846-RRT	CRI			7879-RRT	CRI
10		2846-RRT	VRI			1178-FT	CRI
11		4546-RRT	RRM			222	CRI
12		4546-RRT	CRI
13		4856-RRT	VRI
14		4856-RRT	CRI
15		4859-FFY	VRI
16		4859-FFY	RRM
17		7879-RRT	CRI
18		7879-RRT	RRM
Sheet

 

[SAXON10]

Thank for your help and sorry for the late reply. I got error the following codes 4856-RRT and 4546-RRT the formula return is VRI & RRM but actual result is CRI.

please check and help me.

 

[SAXON10]

AhoyNC : Thanks for your help formula working fine much appreciated.

 

[AhoyNC]

You're welcome. Thanks for the feedback.