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

V

VeKa27

Board Regular
Joined
Sep 11, 2015
Messages
56
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)
StatusMarkTypeFuelPlateKMOmniumOmnium UntilWinter TyresColorRemarks
ActiveBMW320GABC-123-560000Yes30/06/2020NoWhite
Non ActiveMercedesA-ClassUNLABC-321-932000No31/12/2019NoGrey
ActiveAudiA6GABC-222-251000Yes25/05/2020YesBlack
Non ActiveAudiA3UNLABC-333-520Yes25/08/2020YesRed
ActiveVWPassatGABC-555-325000NoNoneNoWhite
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.

AudiNon 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,
 
Sort by date Sort by votes
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
AB
1AudiNon Active
2Type + FuelA3 UNL
3Plate:ABC-333-5
4KM:20
5Omnium + Date Until:Yes 25/08/2020
6Winter TypesYes
7Color:Red
Sheet3
Cell Formulas
RangeFormula
B2B2=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)
B3B3=INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),5)
B4B4=INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),6)
B5B5=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)
B6B6=INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),9)
B7B7=INDEX(Sheet2!A1:K12,SUMPRODUCT((A1=Sheet2!B1:B12)*(B1=Sheet2!A1:A12)*ROW(1:12)),10)
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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..
 
Upvote 0
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
ABCDEFGHIJK
1StatusMarkTypeFuelPlateKMOmniumOmnium UntilWinter TyresColorRemarks
2ActiveBMW320GABC-123-560000Yes30/06/2020NoWhite
3
4
5Non ActiveMercedesA-ClassUNLABC-321-932000No31/12/2019NoGrey
6
7
8ActiveAudiA6GABC-222-251000Yes25/05/2020YesBlack
9
10Non ActiveAudiA3UNLABC-333-520Yes25/08/2020YesRed
11
12ActiveVWPassatGABC-555-325000NoNoneNoWhite
13
14Non ActiveAudiB10UNL2XYZ-222-2100Yes25/5/2020YesBlack
15
16Non ActiveAudiXXXYYYYZZZZ99YES25/10/2020NOWHITE
Sheet2


Book1
ABCD
1AudiNon Active3
2Type + FuelA3 UNLB10 UNL2XXX YYYY
3Plate:ABC-333-5XYZ-222-2ZZZZ
4KM:2010099
5Omnium + Date Until:Yes 25/08/2020Yes 25/5/2020YES 25/10/2020
6Winter TypesYesYesNO
7Color:RedBlackWHITE
Sheet3
Cell Formulas
RangeFormula
C1C1=COUNTIFS(Sheet2!B1:B50,A1,Sheet2!A1:A50,B1)
B2B2{=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:D2C2{=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),"")}
B3B3{=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:D3C3{=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),"")}
B4B4{=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:D4C4{=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),"")}
B5B5{=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:D5C5{=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),"")}
B6B6{=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:D6C6{=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),"")}
B7B7{=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:D7C7{=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.
 
Upvote 0
Wow impressed.. but i don't want to use the array formulas. (see my title..1000 times sorry)
 
Upvote 0
I saw your original, but don't have a solution without arrays. What's wrong with array formulas?
 
Upvote 0
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.
 
Upvote 0
Try the following with a helper colunma ("M") on sheet2.

Book1
ABCDEFGHIJKLM
1StatusMarkTypeFuelPlateKMOmniumOmnium UntilWinter TyresColorRemarksHelper Column
2ActiveBMW320GABC-123-560000Yes30/06/2020NoWhite1
3Non ActiveMercedesA-ClassUNLABC-321-932000No31/12/2019NoGrey1
4ActiveAudiA6GABC-222-251000Yes25/05/2020YesBlack1
5Non ActiveAudiA3UNLABC-333-520Yes25/08/2020YesRed1
6ActiveVWPassatGABC-555-325000NoNoneNoWhite1
7Non ActiveAudiB10UNL2XYZ-222-2100Yes25/05/2020YesBlack2
8Non ActiveAudiXXXYYYYZZZZ99YES25/10/2020NOWHITE3
9
10
11
12AudiNon Active3
13Type + FuelA3 UNLB10 UNL2XXX YYYY 
14PlateABC-333-5XYZ-222-2ZZZZ 
15KM2010099 
16Omnium + Date Until:Yes 44068Yes 43976YES 44129 
17Winter TypesYesYesNO 
18Color:RedBlackWHITE 
Sheet2
Cell Formulas
RangeFormula
M2:M8M2=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)
C12C12=COUNTIFS(Sheet2!A2:A8,B12,Sheet2!B2:B8,A12)
B13:E13B13=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:E14B14=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:E15B15=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:E16B16=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:E17B17=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:E18B18=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),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

Jeffrey Mahoney
  • Question Question
AutoFilter Selected Items - Selecting non-structured Table
Replies
8
Views
634
Akuini
Akuini
K
  • Question Question
Question about VBA macro using Index Match with 2 criteria
Replies
9
Views
616
kiri
K
D
  • Question Question
Help! VBA Index and Match handling multiple criteria using array formula and variable in index range argument
Replies
0
Views
1K
dylon25
D
willow1985
  • Question Question
VBA help with code to filter for 3 contains criteria
Replies
1
Views
1K
steve the fish
steve the fish
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top