2D lookup in an array

[LaurentT]

I'm struggling to find a solution for that problem. I'm looking to lookup on a 2D table made of unit values.
I've made such example below.
I want to parse all rows in below table and find the country associated to an unique identifier.

for instance, if I parse the value 9, I shall get Germany or if I parse 6, I shall get Spain.
All the numbers to parse are on a table with multiple rows

2	5	6	Spain
3	4	8	Italy
1	9	12	Germany
7	11	14	UK

=> I'm expected to get

Item	Country
4	Italy
11	UK
2	Spain
5	Spain

 

[Fluff]

Hi & welcome to MrExcel.
As long as the numbers do not appear more than once, try

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1							Item	Country
2	2	5	6	Spain			4	Italy
3	3	4	8	Italy			11	UK
4	1	9	12	Germany			2	Spain
5	7	11	14	UK			5	Spain
Lists

Cell Formulas
Range	Formula
H2:H5	H2	=INDEX($D$1:$D$5,SUMPRODUCT(($A$2:$C$5=G2)*(ROW($D$2:$D$5))))

 

[Aladin Akyurek]

One way ; control+shift+enter

=CONCAT(IF(A1:C4=G2,D1:D4,""))

where G2 houses a unique input value like 4 and A1:C4 houses values like 2;5;6 etc.

 

[LaurentT]

Thanks. I'll try and let you know !

 

[Fluff]

I'd suggest using Aladin's solution, it's far better.

 

[Peter_SSs]

One way ; control+shift+enter

=CONCAT(IF(A1:C4=G2,D1:D4,""))

where G2 houses a unique input value like 4 and A1:C4 houses values like 2;5;6 etc.

Though with Microsoft 365, the OP will not require control+shift+enter.

 

[LaurentT]

Though with Microsoft 365, the OP will not require control+shift+enter.

thanks and what would be in cells D1:D4 ?

 

[Fluff]

The countries you want to return.

 

[LaurentT]

Thanks !

 

[Fluff]

Glad we could help & thanks for the feedback.