How can I find the code I need based on multiple lookup values in my table, based on data in a refer-CodePudding

I'm trying to find the code for the best match in a table using 4 lookup values.

In the image below the left table A to E is a static table and is the reference table

The right table normally on a separate sheet has multiple lines of varying data, where I need to find the correct code based matching the Name, Age and Age Calc from, using the information in the reference table.

I have tried VLOOKUP and INDEX/MATCH, but I'm struggling with the multiple lookup values.

The answers I need in J are: 5678, 7654, 4679, 7654, 1234. What would be my best method of achieving my code results?

CodePudding user response：

You can do this with a SUMIFS:

=SUMIFS(E:E,A:A,G2,B:B,"<="&H2,C:C,">="&H2,D:D,I2)

CodePudding user response：

Assuming there can be only one match, then do something like:

=SUMIFS($E$2:$E$6,$A$2:$A$6,G2,$B$2:$B$6,"<="&H2,$C$2:$C$6,">="&H2,$D$2:$D$6,I2)