Lookup first match corresponding to any combination of values from multiple criteria lists

[Marbleking]

Hi,

I have a Table 1 with several columns of data. In Table 4 I have some of the data from one of the Table 1 columns. I need to perform a lookup with the data in Table 4 against Table 1 that also satisfies any given criteria given in Table 2 and Table 3. Table 2 and Table 3 contains some of the data from other Table 1 columns than what Table 4 does. For the first match that satisfies all criteria, I need to return the sum of two corresponding columns in Table 1 back to Table 4, in a column adjacent to the matched values. I need the formula for this to return a dynamic array that spills the values down alongside the Table 4 criteria.

Does anyone have a solution?

Best regards,
Marbleking

[oeldere]

After looking for the criteria a filter on the data.

See the attached file.

[Bo_Ry]

Please try at
K4
=UNIQUE(A4:A30)

L4
=MOD(SMALL(XLOOKUP(K4#&TRANSPOSE(G4:G6)&I4,A4:A30&B4:B30&C4:C30,D4:D30+E4:E30,9^5)+SEQUENCE(ROWS(K4#))*10^9,SEQUENCE(ROWS(K4#),,,3)),10^6)

[Marbleking]

Thank you for great and instructive input!

Regards,
Marbleking

[XOR LX]

So there's only ever one entry in Table3?

The fact that you've marked this thread as 'solved' would suggest as much, though just thought I'd ask.

Regards

[oeldere]

Thank you for great and instructive input!

Regards,
Marbleking

You don't add in your reply to whom you are replying.

[Marbleking]

Hi,

Thanks to you, Oeldere, I appreciate your input. Also thanks to Bo_Ry for supplying a solution without helper columns that creates a spilled range.

As to the question asked by XOR LX, I think that's a good question. For my immediate use, there's only going to be one entry in Table 3. But it sure would be useful to see a more generalized solution which takes into account the possibility of several entries in both Table 2 and Table 3, as well as perhaps also additional criteria tables.

The best place to learn about these kinds of formulas have proven to be this very forum, though I'd like to find a resource online that contains learning resources about array formulas like this and others.

Best regards,
Marbleking