Index Match based on exact value through duplicates

[Mavri]

Hello,friends!
I need to extract region from Table 1 to Table 2 only by criteriums (PROJECT) in Table 3 . I tried INDEX/MATCH by name, but it returns me the first value which it finds.
Thanks in advance!

REGION	NAME	PROJECT	REGION	NAME	PROJECT	REGION	PROJECT
COL	ACHCHEY LAL	WIN		ACHCHEY LAL	TEN	COL	TEN
COL	ACHHE LAL PRAJAPATI	WIN		ACHHE LAL PRAJAPATI	ELE	AUT	ELE
AUT	AKHILESH KUMAR	WIN
AUT	DIMPLE MASIH	WIN	Table 2	Table 3
COL	MALKIT SINGH	WIN
COL	ACHCHEY LAL	TEN
AUT	ACHHE LAL PRAJAPATI	ELE
Table 1

 

[ExcelNewbie2020]

Hello,friends!
I need to extract region from Table 1 to Table 2 only by criteriums (PROJECT) in Table 3 . I tried INDEX/MATCH by name, but it returns me the first value which it finds.
Thanks in advance!

REGION	NAME	PROJECT	REGION	NAME	PROJECT	REGION	PROJECT
COL	ACHCHEY LAL	WIN		ACHCHEY LAL	TEN	COL	TEN
COL	ACHHE LAL PRAJAPATI	WIN		ACHHE LAL PRAJAPATI	ELE	AUT	ELE
AUT	AKHILESH KUMAR	WIN
AUT	DIMPLE MASIH	WIN	Table 2	Table 3
COL	MALKIT SINGH	WIN
COL	ACHCHEY LAL	TEN
AUT	ACHHE LAL PRAJAPATI	ELE
Table 1

could you please show your expected output?

 

[Mavri]

could you please show your expected output?

Sorry,I didn't put exact Table 1.

REGION	NAME	PROJECT	REGION	NAME	PROJECT	REGION	PROJECT
GRE	ACHCHEY LAL	WIN		ACHCHEY LAL	TEN	COL	TEN
GRE	ACHHE LAL PRAJAPATI	WIN		ACHHE LAL PRAJAPATI	ELE	AUT	ELE
AUT	AKHILESH KUMAR	WIN
AUT	DIMPLE MASIH	WIN	Table 2	Table 3
COL	MALKIT SINGH	WIN
COL	ACHCHEY LAL	TEN
AUT	ACHHE LAL PRAJAPATI	ELE
Table 1

the output in Table 2 should be

REGION	NAME	PROJECT
COL	ACHCHEY LAL	TEN
AUT	ACHHE LAL PRAJAPATI	ELE
Table 2

 

[Mavri]

Maybe
it's a little clearer this way

REGION	NAME	PROJECT
GRE	ACHCHEY LAL	WIN
GRE	ACHHE LAL PRAJAPATI	WIN
AUT	AKHILESH KUMAR	WIN
AUT	DIMPLE MASIH	WIN
COL	MALKIT SINGH	WIN
COL	ACHCHEY LAL	TEN
AUT	ACHHE LAL PRAJAPATI	ELE
Table 1
REGION	NAME	PROJECT
COL	ACHCHEY LAL	TEN
AUT	ACHHE LAL PRAJAPATI	ELE
Table 2
REGION	PROJECT
COL	TEN
AUT	ELE
Table 3

 

[ExcelNewbie2020]

Maybe
it's a little clearer this way

REGION	NAME	PROJECT
GRE	ACHCHEY LAL	WIN
GRE	ACHHE LAL PRAJAPATI	WIN
AUT	AKHILESH KUMAR	WIN
AUT	DIMPLE MASIH	WIN
COL	MALKIT SINGH	WIN
COL	ACHCHEY LAL	TEN
AUT	ACHHE LAL PRAJAPATI	ELE
Table 1
REGION	NAME	PROJECT
COL	ACHCHEY LAL	TEN
AUT	ACHHE LAL PRAJAPATI	ELE
Table 2
REGION	PROJECT
COL	TEN
AUT	ELE
Table 3

its a bit confusing.. at first you want to extract the REGION but your table 2 is consist of REGION NAME and PROJECT?

 

[Mavri]

its a bit confusing.. at first you want to extract the REGION but your table 2 is consist of REGION NAME and PROJECT?

In Table 1 there are Region,Name and Project and there are plenty of duplicate names with different Regions and Projects.In Table 2 I'd like to extract Region based on name but only by criteriums in Table 3( I want Region "COL" if Project is "TEN" and I'd like Region "AUT" if Project is "ELE").But when I try with index/match function,it returns me the first value for ACHCHEY LAL- in this case - REGION "GRE" because it comes first in Table 1.How can I count Region for ACHCHEY LAL only if his project in Table 1 is "TEN".
I hope I explained little bit better

 

[ExcelNewbie2020]

In Table 1 there are Region,Name and Project and there are plenty of duplicate names with different Regions and Projects.In Table 2 I'd like to extract Region based on name but only by criteriums in Table 3( I want Region "COL" if Project is "TEN" and I'd like Region "AUT" if Project is "ELE").But when I try with index/match function,it returns me the first value for ACHCHEY LAL- in this case - REGION "GRE" because it comes first in Table 1.How can I count Region for ACHCHEY LAL only if his project in Table 1 is "TEN".
I hope I explained little bit better

like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	REGION	NAME	PROJECT			REGION	NAME	PROJECT				REGION	PROJECT
2	GRE	ACHCHEY LAL	WIN			COL	ACHCHEY LAL	TEN				COL	TEN
3	GRE	ACHHE LAL PRAJAPATI	WIN			AUT	ACHHE LAL PRAJAPATI	ELE				AUT	ELE
4	AUT	AKHILESH KUMAR	WIN
5	AUT	DIMPLE MASIH	WIN
6	COL	MALKIT SINGH	WIN
7	COL	ACHCHEY LAL	TEN
8	AUT	ACHHE LAL PRAJAPATI	ELE
9
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=INDEX($A$2:$A$8,MATCH(1,(G2=$B$2:$B$8)*(M2=$C$2:$C$8),0))

 

[Mavri]

like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	REGION	NAME	PROJECT			REGION	NAME	PROJECT				REGION	PROJECT
2	GRE	ACHCHEY LAL	WIN			COL	ACHCHEY LAL	TEN				COL	TEN
3	GRE	ACHHE LAL PRAJAPATI	WIN			AUT	ACHHE LAL PRAJAPATI	ELE				AUT	ELE
4	AUT	AKHILESH KUMAR	WIN
5	AUT	DIMPLE MASIH	WIN
6	COL	MALKIT SINGH	WIN
7	COL	ACHCHEY LAL	TEN
8	AUT	ACHHE LAL PRAJAPATI	ELE
9
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=INDEX($A like this? Book1 A B C D E F G H I J K L M N O 1 REGION NAME PROJECT REGION NAME PROJECT REGION PROJECT 2 GRE ACHCHEY LAL WIN COL ACHCHEY LAL TEN COL TEN 3 GRE ACHHE LAL PRAJAPATI WIN AUT ACHHE LAL PRAJAPATI ELE AUT ELE 4 AUT AKHILESH KUMAR WIN 5 AUT DIMPLE MASIH WIN 6 COL MALKIT SINGH WIN 7 COL ACHCHEY LAL TEN 8 AUT ACHHE LAL PRAJAPATI ELE 9 Sheet1 Cell Formulas Range Formula F2:F3 F2 =INDEX($A$2:$A$8,MATCH(1,(G2=$B$2:$B$8)*(M2=$C$2:$C$8),0)) Like that, but what if I have more different projects like in the table down,and I need returned value only in that cases,and if there is no that project in that region it should return me error REGION PROJECT COL TEN AUT ELE COL TWO AUT TWE AUT ONE COL THR $2:$A$8,MATCH(1,(G2=$B$2:$B$8)*(M2=$C$2:$C$8),0))

 

[ExcelNewbie2020]

if that is the case.. maybe you don't need the table 1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	REGION	NAME	PROJECT			REGION	NAME	PROJECT				REGION	PROJECT
2	GRE	ACHCHEY LAL	WIN			COL	ACHCHEY LAL	TEN				COL	TEN
3	GRE	ACHHE LAL PRAJAPATI	WIN			AUT	ACHHE LAL PRAJAPATI	ELE				AUT	ELE
4	AUT	AKHILESH KUMAR	WIN									COL	TWO
5	AUT	DIMPLE MASIH	WIN									AUT	TWE
6	COL	MALKIT SINGH	WIN									AUT	ONE
7	COL	ACHCHEY LAL	TEN									COL	THR
8	AUT	ACHHE LAL PRAJAPATI	ELE
9
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=INDEX($L$2:$L$8,MATCH(H2,$M$2:$M$8,0))

 

[Mavri]

if that is the case.. maybe you don't need the table 1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	REGION	NAME	PROJECT			REGION	NAME	PROJECT				REGION	PROJECT
2	GRE	ACHCHEY LAL	WIN			COL	ACHCHEY LAL	TEN				COL	TEN
3	GRE	ACHHE LAL PRAJAPATI	WIN			AUT	ACHHE LAL PRAJAPATI	ELE				AUT	ELE
4	AUT	AKHILESH KUMAR	WIN									COL	TWO
5	AUT	DIMPLE MASIH	WIN									AUT	TWE
6	COL	MALKIT SINGH	WIN									AUT	ONE
7	COL	ACHCHEY LAL	TEN									COL	THR
8	AUT	ACHHE LAL PRAJAPATI	ELE
9
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=INDEX($L$2:$L$8,MATCH(H2,$M$2:$M$8,0))

I would like that I dont need it,but I have it In Table 1 there are a plenty of data(Names,passport numbers,regions and projects),and in the Table 2 is small part which is in some cases duplicated in Table 1..The only difference is that in Table 1 the regions and projects are different for the same people.Only duplicated are names or passport numbers.And I woud like to get the aproppriate region from table 1,by name(or passport number) only by project in Table 3,and when it when it comes to a certain man and the asked project is not what I need,it should skip it.If thats the only time when man is in Table 1 it shoud return error(just like it didnt found him) .But in table 3 one region have 3 or more projects.
I hope it makes sense now