Table problems

[Atanas Kisyov]

Hello guys!

I have a problem that I cannot go around and I really hope someone can help me.

I have two tables with material information that look like this:

Table1

Table2

The first column is the finished good number. I have different finished good numbers but they are made with the same materials.
In some cases not all materials are in the same order, so I decided to sum all the numbers to find matches.
The problem here is that VLookup only finds the first match.

And the result is:

But I need it to be like this:

Can someone help me with this problem?

 

[KRice]

Could you paste the VLOOKUP formula that you are using, please? I do not completely understand what you are trying to do. In this example, I believe you want to use a lookup value of "11111" (but where is that lookup value located?) and then list all table entries in Table 2 whose Sum in Table 2 matches that of "11111" in Table 2 (in this case, 1371725). More information about the rows and columns is needed to advise further.

 

[KRice]

I suspect you may want something like this. Here the lookup value (77777) is in the blue cell. Column A in Table 2 is searched for a match to 77777 (the formula assumes there is only one match because only the first match will be returned in this step). Then the Sum associated with that match is determined. That Sum amount is then used to search Column G in Table 2 for any other matching sum values...and all of those rows are returned in the Results table.

MrExcel_20200618.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Table2								Results Table		Lookup	77777
2	Finished Good Number	Matl1	Matl2	Matl3	Matl4	Matl5	Sum		Finished Good Number	Matl1	Matl2	Matl3	Matl4	Matl5	Sum
3	11111	123	1234	12345	123456	1234567	1371725		55555	321	1234	12345	123456	1234567	1371923
4	22222	123	1234	12345	123456	1234567	1371725		66666	321	1234	12345	123456	1234567	1371923
5	33333	123	1234	12345	123456	1234567	1371725		77777	321	1234	12345	123456	1234567	1371923
6	44444	123	1234	12345	123456	1234567	1371725		88888	321	1234	12345	123456	1234567	1371923
7	55555	321	1234	12345	123456	1234567	1371923
8	66666	321	1234	12345	123456	1234567	1371923
9	77777	321	1234	12345	123456	1234567	1371923
10	88888	321	1234	12345	123456	1234567	1371923
Sheet3

Cell Formulas
Range	Formula
I3:O10	I3	=IFERROR(INDEX(A$3:A$10,AGGREGATE(15,6,(ROW($A$3:$A$10)-ROW($A$2))/($G$3:$G$10=INDEX($G$3:$G$10,MATCH($L$1,$A$3:$A$10,0))),ROWS(I$3:I3))),"")
G3:G10	G3	=SUM(B3:F3)

You can click on the clipboard icon in the upper left corner of the table (intersection of row/column headers) to copy this to your clipboard for pasting into your file to try it out.

 

[Atanas Kisyov]

Yes, this works perfectly. Thank you!

 

[Atanas Kisyov]

Yes, this works perfectly. Thank you!

 

[KRice]

You're welcome...thanks for the update.