Formula Index-Match with 2 criteria NON array and NON VBA

[VeKa27]

Hi Guys,
Need your help on this one.. I have a tabel that looks like this one: (data can be filled in with blanco lines between)

Status	Mark	Type	Fuel	Plate	KM	Omnium	Omnium Until	Winter Tyres	Color	Remarks
Active	BMW	320	G	ABC-123-5	60000	Yes	30/06/2020	No	White
Non Active	Mercedes	A-Class	UNL	ABC-321-9	32000	No	31/12/2019	No	Grey
Active	Audi	A6	G	ABC-222-2	51000	Yes	25/05/2020	Yes	Black
Non Active	Audi	A3	UNL	ABC-333-5	20	Yes	25/08/2020	Yes	Red
Active	VW	Passat	G	ABC-555-3	25000	No	None	No	White

Now i have another litle tabel where the user needs to lookup some value on 2 criteria. First the markname and second the status of the vehicle.

Audi	Non Active
Type + Fuel:	G
Plate:
KM:
Omnium + Date Until:
Winter Tyres:
Color:

So, the field where now "Audi" stands is selectable on all marks. For example if the user chooses "Audi".
The field besides is also selectable between "Active and Non Active".
After selection, the information has to be automatic filled in on the 2 selectable criterias. (in example above, we must seek the information of: Audi, Non Active. Type + Fuel: has to be "A3 UNL", Plate: has to be "ABC-333-5", and so further on..)

I tried many times vith INDEX and MATCH but i can't fix the formulas on the 2 criterias.
Important: I can't use array function in formula an i don't want to use VBA.

Someone smart enough to fix the formulas for the fields?

Allready Big Thanks for your help,

 

[kweaver]

Probably something like this will work with your source data in Sheet2 in my example. The assumption is that the user filles in A1 and B1 on this sheet.

Book1
	A	B
1	Audi	Non Active
2	Type + Fuel	A3 UNL
3	Plate:	ABC-333-5
4	KM:	20
5	Omnium + Date Until:	Yes 25/08/2020
6	Winter Types	Yes
7	Color:	Red
Sheet3

Cell Formulas
Range	Formula
B2	B2	=INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),3)&" "&INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),4)
B3	B3	=INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),5)
B4	B4	=INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),6)
B5	B5	=INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),7)&" "&INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),8)
B6	B6	=INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),9)
B7	B7	=INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),10)

 

[VeKa27]

Wow, Kweaver, that was fast and most important, it works.. (you're great!)
An extra question. Suppose there are more then 1 Audi that is Non Active. And i have on sheet3 multiple search tables. Is it than possible to deliver the search results of car2 in table2 and if there is a 3th Audi non active, to give the search results in table3? Table 2 and 3 will have the same lay-out as table1 (Mark, Type+Fuel, Plate, ...)

I suppose with your formulas it wil always be the first result that will be given. In my question i want also the 2nd, 3th, 4th ... (as many as i make search tables.

Hope you understand what i mean?

 

[kweaver]

First part: what would you want as a result if there are more than 1 Audi/Non-Active on the first sheet? How many duplicates might be possible?

 

[VeKa27]

Maximum 10. (i will make 10 search tables)
There can be, for example 5 Audi's be on Non Active but they will all have different colors, Plates, ...
In Table1 i want to see the data of Audi1, in Table2 i want to see the data of Audi2 an so on..

 

[kweaver]

Here's part of it, I think. I'm sure there are better ways, but this was my quick & dirty to solve one problem. Fill D2:D7 across.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Status	Mark	Type	Fuel	Plate	KM	Omnium	Omnium Until	Winter Tyres	Color	Remarks
2	Active	BMW	320	G	ABC-123-5	60000	Yes	30/06/2020	No	White
3
4
5	Non Active	Mercedes	A-Class	UNL	ABC-321-9	32000	No	31/12/2019	No	Grey
6
7
8	Active	Audi	A6	G	ABC-222-2	51000	Yes	25/05/2020	Yes	Black
9
10	Non Active	Audi	A3	UNL	ABC-333-5	20	Yes	25/08/2020	Yes	Red
11
12	Active	VW	Passat	G	ABC-555-3	25000	No	None	No	White
13
14	Non Active	Audi	B10	UNL2	XYZ-222-2	100	Yes	25/5/2020	Yes	Black
15
16	Non Active	Audi	XXX	YYYY	ZZZZ	99	YES	25/10/2020	NO	WHITE
Sheet2

Book1
	A	B	C	D
1	Audi	Non Active	3
2	Type + Fuel	A3 UNL	B10 UNL2	XXX YYYY
3	Plate:	ABC-333-5	XYZ-222-2	ZZZZ
4	KM:	20	100	99
5	Omnium + Date Until:	Yes 25/08/2020	Yes 25/5/2020	YES 25/10/2020
6	Winter Types	Yes	Yes	NO
7	Color:	Red	Black	WHITE
Sheet3

Cell Formulas
Range	Formula
C1	C1	=COUNTIFS(Sheet2!B1:B50,A1,Sheet2!A1:A50,B1)
B2	B2	{=INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW(1:50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW(1:50)),1),3)&" "&INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW(1:50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW(1:50)),1),4)}
C2:D2	C2	{=IF($C$1>=COLUMN()-1,INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW(1:50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW(1:50)),COLUMN()-1),3)&" "&INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW(1:50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW(1:50)),COLUMN()-1),4),"")}
B3	B3	{=INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),1),5)}
C3:D3	C3	{=IF($C$1>=COLUMN()-1,INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),COLUMN()-1),5),"")}
B4	B4	{=INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),1),6)}
C4:D4	C4	{=IF($C$1>=COLUMN()-1,INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),COLUMN()-1),6),"")}
B5	B5	{=INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),1),7)&" "&INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),1),8)}
C5:D5	C5	{=IF($C$1>=COLUMN()-2,INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),COLUMN()-1),7)&" "&INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),COLUMN()-1),8),"")}
B6	B6	{=INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),1),9)}
C6:D6	C6	{=IF($C$1>=COLUMN()-1,INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),COLUMN()-1),9),"")}
B7	B7	{=INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),1),10)}
C7:D7	C7	{=IF($C$1>=COLUMN()-1,INDEX(Sheet2!$A$1:$K$50,AGGREGATE(15,6,IF((($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50))>0,($A$1=Sheet2!$B$1:$B$50)*($B$1=Sheet2!$A$1:$A$50)*ROW($1:$50)),COLUMN()-1),10),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[VeKa27]

Wow impressed.. but i don't want to use the array formulas. (see my title..1000 times sorry)

 

[kweaver]

I saw your original, but don't have a solution without arrays. What's wrong with array formulas?

 

[VeKa27]

It is a shared workbook and array formulas gives often problems in shared condition.. For that reason i try to avoid array formulas as much as i can..
But no problem, i will find my way out without arrays. And if i am lucky someone else will read my post and gives a solution.

 

[DanteAmor]

Try the following with a helper colunma ("M") on sheet2.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Status	Mark	Type	Fuel	Plate	KM	Omnium	Omnium Until	Winter Tyres	Color	Remarks		Helper Column
2	Active	BMW	320	G	ABC-123-5	60000	Yes	30/06/2020	No	White			1
3	Non Active	Mercedes	A-Class	UNL	ABC-321-9	32000	No	31/12/2019	No	Grey			1
4	Active	Audi	A6	G	ABC-222-2	51000	Yes	25/05/2020	Yes	Black			1
5	Non Active	Audi	A3	UNL	ABC-333-5	20	Yes	25/08/2020	Yes	Red			1
6	Active	VW	Passat	G	ABC-555-3	25000	No	None	No	White			1
7	Non Active	Audi	B10	UNL2	XYZ-222-2	100	Yes	25/05/2020	Yes	Black			2
8	Non Active	Audi	XXX	YYYY	ZZZZ	99	YES	25/10/2020	NO	WHITE			3
9
10
11
12	Audi	Non Active	3
13	Type + Fuel	A3 UNL	B10 UNL2	XXX YYYY
14	Plate	ABC-333-5	XYZ-222-2	ZZZZ
15	KM	20	100	99
16	Omnium + Date Until:	Yes 44068	Yes 43976	YES 44129
17	Winter Types	Yes	Yes	NO
18	Color:	Red	Black	WHITE
Sheet2

Cell Formulas
Range	Formula
M2:M8	M2	=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)
C12	C12	=COUNTIFS(Sheet2!A2:A8,B12,Sheet2!B2:B8,A12)
B13:E13	B13	=IFERROR(LOOKUP(2,1/(($A$2:$A$8=$B$12)*($B$2:$B$8=$A$12)*($M$2:$M$8=COLUMNS($B$12:B12))),$C$2:$C$8)&" "& LOOKUP(2,1/(($A$2:$A$8=$B$12)*($B$2:$B$8=$A$12)*($M$2:$M$8=COLUMNS($B$12:B12))),$D$2:$D$8),"")
B14:E14	B14	=IFERROR(LOOKUP(2,1/(($A$2:$A$8=$B$12)*($B$2:$B$8=$A$12)*($M$2:$M$8=COLUMNS($B$12:B13))),$E$2:$E$8),"")
B15:E15	B15	=IFERROR(LOOKUP(2,1/(($A$2:$A$8=$B$12)*($B$2:$B$8=$A$12)*($M$2:$M$8=COLUMNS($B$12:B14))),$F$2:$F$8),"")
B16:E16	B16	=IFERROR(LOOKUP(2,1/(($A$2:$A$8=$B$12)*($B$2:$B$8=$A$12)*($M$2:$M$8=COLUMNS($B$12:B15))),$G$2:$G$8)&" "& LOOKUP(2,1/(($A$2:$A$8=$B$12)*($B$2:$B$8=$A$12)*($M$2:$M$8=COLUMNS($B$12:B15))),$H$2:$H$8),"")
B17:E17	B17	=IFERROR(LOOKUP(2,1/(($A$2:$A$8=$B$12)*($B$2:$B$8=$A$12)*($M$2:$M$8=COLUMNS($B$12:B16))),$I$2:$I$8),"")
B18:E18	B18	=IFERROR(LOOKUP(2,1/(($A$2:$A$8=$B$12)*($B$2:$B$8=$A$12)*($M$2:$M$8=COLUMNS($B$12:B17))),$J$2:$J$8),"")