Index Match Multiple Criteria and Return Multiple Unique Values

[rvande22]

I'm trying to create an array formula based on data in 2 separate tables. The formula should evaluate the data in table 2 and look for exact matches based on the data in table 1. When it finds an exact match, it will return the value in the "Order #" column in table 2 to the "Order #" column in table 1. Once the formula finds the unique value in table 2, it should then look for the next unique value in table 2 that matches all the criteria in table 1.

Table 1 below shows the desired result. The formula will be in table 1 "Order #" column. In the example below, the formula looked in table 2 for an exact match (12258, Red, Tan) and found two values (268, 657). It returned each unique matching value to the "Order #" column in table 1.

I'm able to use an Index Match formula to evaluate for multiple criteria, but I can't figure out how to find a matching value in table 2, then continue evaluating table 2 to find the next matching value. I can only find the first matching value (as shown in my "Error Table" The purpose in doing this is to match customer orders with product that has already been produced and can be shipped immediately. Your help is greatly appreciated!

Table 1
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Model[/TD]
[TD]Ext Color[/TD]
[TD]Int Color[/TD]
[/TR]
[TR]
[TD]268[/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD]657[/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
</tbody>[/TABLE]








Table 2
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Model[/TD]
[TD]Ext Color[/TD]
[TD]Int Color[/TD]
[/TR]
[TR]
[TD]268[/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD]385[/TD]
[TD]12258[/TD]
[TD]Black[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD]657[/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD]915[/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Orange[/TD]
[/TR]
</tbody>[/TABLE]








ERROR Table
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Model[/TD]
[TD]Ext Color[/TD]
[TD]Int Color[/TD]
[/TR]
[TR]
[TD]268[/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD]268[/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD]268[/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD]268[/TD]
[TD]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

maybe something like...

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Table 1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Order #[/TD]
[TD]Model[/TD]
[TD]Ext Color[/TD]
[TD]Int Color[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]268[/TD]
[TD="align: right"]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]657[/TD]
[TD="align: right"]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD="align: right"]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD="align: right"]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Table 2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Order #[/TD]
[TD]Model[/TD]
[TD]Ext Color[/TD]
[TD]Int Color[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]268[/TD]
[TD="align: right"]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]385[/TD]
[TD="align: right"]12258[/TD]
[TD]Black[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]657[/TD]
[TD="align: right"]12258[/TD]
[TD]Red[/TD]
[TD]Tan[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]915[/TD]
[TD="align: right"]12258[/TD]
[TD]Red[/TD]
[TD]Orange[/TD]
[/TR]
</tbody>[/TABLE]

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]=IFERROR(INDEX($A$10:$A$13,AGGREGATE(15,6,(ROW($A$10:$A$13)-ROW($A$10)+1)/(($B$10:$B$13=B3)*($C$10:$C$13=C3)*($D$10:$D$13=D3)),ROWS($A$3:A3))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[rvande22]

Thanks Weazel...the tables are in different workbooks and the data is generated in each table using VBA code that extracts data from the mainframe. Each table contains about 50 columns and up to 60K or 70K rows. I'm only attempting to extract a small amount of data, but the raw data file is pretty big. I guess I'm just saying I can't easily combine the tables.

 

[Weazel]

i'm not sure i follow, the tables aren't combined.

you would need to modify the formula depending on how your data is set up.