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

Order #	Model	Ext Color	Int Color
268	12258	Red	Tan
657	12258	Red	Tan
	12258	Red	Tan
	12258	Red	Tan

<tbody>
</tbody>

Table 2

Order #	Model	Ext Color	Int Color
268	12258	Red	Tan
385	12258	Black	Tan
657	12258	Red	Tan
915	12258	Red	Orange

<tbody>
</tbody>

ERROR Table

Order #	Model	Ext Color	Int Color
268	12258	Red	Tan
268	12258	Red	Tan
268	12258	Red	Tan
268	12258	Red	Tan

<tbody>
</tbody>

 

[Weazel]

maybe something like...

	A	B	C	D
1	Table 1
2	Order #	Model	Ext Color	Int Color
3	268	12258	Red	Tan
4	657	12258	Red	Tan
5		12258	Red	Tan
6		12258	Red	Tan
7
8	Table 2
9	Order #	Model	Ext Color	Int Color
10	268	12258	Red	Tan
11	385	12258	Black	Tan
12	657	12258	Red	Tan
13	915	12258	Red	Orange

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula A3 =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))),"") <tbody> </tbody>

<tbody>
</tbody>

 

[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.