Lookup duplicate value with multiple criteria

[ExcelNewbie2020]

Sir,

I have this table i need to transform..

it should be based on country (that can be duplicate) and based on qty that is greater than 0

LOC = Criteria are Country and qty greater 0

MODEL = Criteria are Country and qty greater 0

QTY = Criteria are Country, LOC and MODEL

Book2
	A	B	C	D	E	F
1		LOC 1	LOC 2	LOC 3	LOC 4	LOC 5
2	APPLE		10			5
3	MAC		3	5	5
4	NOKIA
5	SAMSUNG	3	3
6		Argentina	Afghanistan	Yemen	Bahrain	Burundi
7
8
9
10
11
12		FORMULA HERE
13	COUNTRY	LOC	MODEL	QTY
14	Argentina	LOC 1	SAMSUNG	3
15	Afghanistan	LOC 2	APPLE	10
16	Afghanistan	LOC 2	MAC	3
17	Afghanistan	LOC 2	SAMSUNG	3
18	Yemen	LOC 3	MAC	5
19	Bahrain	LOC 4	MAC	5
20	Burundi	LOC 5	APPLE	5
Sheet2

thank you very much

 

[Fluff]

How about

=LET(d,B2:F5,HSTACK(TOCOL(IF(d<>"",B6:F6,1/0),2,1),TOCOL(IF(d<>"",B1:F1,1/0),2,1),TOCOL(IF(d<>"",A2:A5,1/0),2,1),TOCOL(d,1,1)))

 

[ExcelNewbie2020]

How about

=LET(d,B2:F5,HSTACK(TOCOL(IF(d<>"",B6:F6,1/0),2,1),TOCOL(IF(d<>"",B1:F1,1/0),2,1),TOCOL(IF(d<>"",A2:A5,1/0),2,1),TOCOL(d,1,1)))

it did worked.. thank you but what if we consider the country A14:A20 as a criteria?

 

[Fluff]

In what way?

 

[ExcelNewbie2020]

In what way?

i mean, instead of spill array, we use the country from range A14:A20 as a lookup value

 

[Fluff]

Will the countries in A14 down always exactly match the number of locations?

 

[ExcelNewbie2020]

Will the countries in A14 down always exactly match the number of locations?

Sometimes there will be duplicate countries with different locations

 

[Fluff]

That does not answer my question.

 

[ExcelNewbie2020]

That does not answer my question.

My bad, i misread.. yes it will always match the number of locations

 

[Fluff]

In that case why do you need to lookup the values, rather than just return all the data like the formula I suggested?