Multiple Criteria Lookup

[nburaq]

Hi,
I have two tables. First one, the table extracted from pivot table ( which is not ordered how I want ) and second one is empty table (which How I want to represent for "Found" values). I have so far tried filter and index match combination but I could not succeed. It might be the reason my mind is so messy today. I will be so glad if you could help me.

Thanks in advance!

1st table: (Range is A1:G13)

Location	D5	B1	B2	B3	A1	B4
ITALY
Found	320​	191​	206​	232​	244​	200​
Not Found	1​	4​	7​	8​	22​	41​
FRANCE
Found	46​	35​	20​	15​	16​	4​
Not Found	62​	89​	81​	61​	129​	58​
GERMANY
Found	183​	167​	163​	27​	76​	24​
Not Found	74​	83​	92​	227​	176​	230​
AUSTRIA
Found	180​	129​	186​	100​	131​	157​
Not Found	18​	80​	48​	61​	72​	51​

2nd table (Range is K1:Q5)

Found	A1	B1	B2	B3	B4	D5
AUSTRIA
FRANCE
GERMANY
ITALY

 

[Peter_SSs]

Is this what you mean?

23 09 11.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Location	D5	B1	B2	B3	A1	B4				Found	A1	B1	B2	B3	B4	D5
2	ITALY										AUSTRIA	131	129	186	100	157	180
3	Found	320	191	206	232	244	200				FRANCE	16	35	20	15	4	46
4	Not Found	1	4	7	8	22	41				GERMANY	76	167	163	27	24	183
5	FRANCE										ITALY	244	191	206	232	200	320
6	Found	46	35	20	15	16	4
7	Not Found	62	89	81	61	129	58
8	GERMANY
9	Found	183	167	163	27	76	24
10	Not Found	74	83	92	227	176	230
11	AUSTRIA
12	Found	180	129	186	100	131	157
13	Not Found	18	80	48	61	72	51
nburaq

Cell Formulas
Range	Formula
L2:Q5	L2	=INDEX($B$2:$G$13,MATCH($K2:$K5,$A$2:$A$13,0)+1,MATCH(L$1:Q$1,$B$1:$G$1,0))
Dynamic array formulas.

 

[nburaq]

Perfect! Thanks for the solution. This is exactly what I was looking for.

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

 

[nburaq]

You are welcome. Thanks for the follow-up.

Just to end my curiosity and do some brain storm, is it possible to apply this formula to solve the same problem
=INDEX($A$2:$G$13;MATCH(1;(($A$2:$A$13="Found")*($A$2:$A$13=$K2)*($B$1:$G$1=$L1);0))
because it seemed to me as multiple criteria issue
Thanks again

 

[Peter_SSs]

Just to end my curiosity and do some brain storm, is it possible to apply this formula to solve the same problem
=INDEX($A$2:$G$13;MATCH(1;(($A$2:$A$13="Found")*($A$2:$A$13=$K2)*($B$1:$G$1=$L1);0))
because it seemed to me as multiple criteria issue

That formula does not make sense to me

($A$2:$A$13="Found")*($A$2:$A$13=$K2)

Assuming K2 is "AUSTRIA", how can any cell in A2:A13 be equal to both "Found" and "AUSTRIA"?

You could use this formula in L2

=INDEX($B$3:$G$13,MATCH(1,($A$3:$A$13="Found")*($A$2:$A$12=$K2),0),MATCH(L$1,$B$1:$G$1,0))

or this one

=SUMPRODUCT(($A$3:$A$13="Found")*($A$2:$A$12=$K2)*($B$1:$G$1=L$1),$B$3:$G$13)

but both of these would need to be copied to all the other cells in the result range whereas the post #2 formula only needs to be place in cell L2

 

[nburaq]

Sorry for my late reply but you are absolutely right both formula also works. I will use it first solution since it is easier to apply.