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

[BradleyS]

I'm trying to find the correct code which include numbers and letters using a lookup table and checking it against 4 lookup values.

In the example 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 but can't get it to work with multiple values. I have had some success with INDEX/MATCH, but I'm struggling with the how to determine the age between the age range values. I have got to here: =INDEX(E2:E6,MATCH(G2&I2,A2:A6&D2:D6,0)), but can't workout how to include the age lookup

I also tried SUMIFS(E2:E6,A2:A6,G2,B2:B6,"<="&H2,C2:C6,">="&H2,D2:D6,I2) which works, but only where the codes are numbers. It fails when they have letters in the code

The answers I need in J are: 567a8, 76a54, 4679, 76a54, 12a34. What would be my best method of achieving my code results?

 

[jasonb75]

Try

=FILTER(E$2:E$6,(A$2:A$6=G2)*(B$2:B$6<=H2)*(C$2:C$6>=H2)*(D$2:D$6=I2))

 

[BradleyS]

Wow I have never used or known about FILTER. Is this available in Excel 2010 as I need to run it on a system that only uses 2010

 

[jasonb75]

Is this available in Excel 2010

Only in office 365 at the moment. Excel 2019 and older do not have it. This should work as far back as 2010, but nothing older than that.

=INDEX(E:E,AGGREGATE(15,6,ROW(E$2:E$6)/(A$2:A$6=G2)/(B$2:B$6<=H2)/(C$2:C$6>=H2)/(D$2:D$6=I2),1))

 

[BradleyS]

Wow another one I have never used AGGREGATE. Thank goodness for clever people like you. Thank you for this it works a treat

 

[Peter_SSs]

If the following applies to your reference table ..
- age bands do not overlap for a particular text/date combination (as it seems for your small sample)
- the data is sorted by name, Age Calc From and Age from (as it seems your small sample might be)
then you could possibly also use this.

Note the red instruction at the bottom.

21 10 22.xlsm
	A	B	C	D	E	F	G	H	I	J
2	TEXT1	6	49	31/03/2022	a		TEXT1	52	31/03/2022	b
3	TEXT1	50	120	31/03/2022	b		TEXT2	55	31/03/2022	d
4	TEXT2	6	49	31/03/2022	c		TEXT3	4	31/03/2022	e
5	TEXT2	50	120	31/03/2022	d		TEXT2	67	31/03/2022	d
6	TEXT3	0	5	31/03/2022	e		TEXT1	48	31/03/2022	a
Lookup

Cell Formulas
Range	Formula
J2:J6	J2	=LOOKUP(H2,IF(A$2:A$6=G2,IF(D$2:D$6=I2,B$2:E$6)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Alex Blakenburg]

Wouldn't this work, without using an array formula.

=LOOKUP(2,1/((A$2:A$6=G2)*(B$2:B$6<=H2)*(C$2:C$6>=H2)*(D$2:D$6=I2)),E$2:E$6)

 

[BradleyS]

Wouldn't this work, without using an array formula.

=LOOKUP(2,1/((A$2:A$6=G2)*(B$2:B$6<=H2)*(C$2:C$6>=H2)*(D$2:D$6=I2)),E$2:E$6)

Yes this also works a treat, and to my small mind is actually easier for me to understand. Thank you all.

 

[Alex Blakenburg]

Haha, mine too. Jason laid the ground work with his FILTER formula

 

[jasonb75]

Wouldn't this work, without using an array formula.

Or this

=LOOKUP(H2,B$2:B$6/(A$2:A$6=G2)/(D$2:D$6=I2),E$2:E$6)

Not tested beyond the basic example provided, but using the same assumptions as Peter, I believe it should be fine.