Lookup based on calculation of multiple criteria

[bujabuja]

Hi,

I have a table that looks like this:

Item	Class	Start	End
Item1	A	50	80
Item2	A	60	150
Item3	A	120	160
Item4	A	150	250
Item5	B	300	400
Item6	B	400	500
Item7	C	50	300
Item8	C	70	150
Item9	C	150	300

<tbody>
</tbody>

If my lookup looks like this:

	Class	Location
Lookup	A	70

<tbody>
</tbody>

I am looking for a function that will give me the Item that meets the criteria Class=A, Location >= Start, Location <= End.

This can be achieved via =index($A$2:$A$10;small(if((A=$B$2:$B$10)*(Location>=$C$2:$C$10)*(Location<=$D$2:$D$10);Row($A$2:$A$10)-1;"");1))

However, if there are multiple matches for this formula I would like to order them with (Location-Start)*(End-Location) as criteria going from highest to lowest. If at all possible I would like to do this without extra columns added.

Thank you for your input.

 

[Eric W]

Welcome to the Board.

It would help if you could give an example of your desired output. But here's one possibility:

	A	B	C	D	E	F	G	H	I	J
1	Item	Class	Start	End				Class	Location
2	Item1	A	50	80			Item3	A	155
3	Item2	A	60	150			Item4
4	Item3	A	120	160
5	Item4	A	150	250
6	Item5	B	300	400
7	Item6	B	400	500
8	Item7	C	50	300
9	Item8	C	70	150
10	Item9	C	150	300

<tbody>
</tbody>

Sheet10​

Array Formulas Cell Formula G2 {=IFERROR(INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=$H$2,IF($C$2:$C$10<=$I$2,IF($D$2:$D$10>= $I$2,IF(COUNTIF($G$1:$G1,$A$2:$A$10)=0,ROW($A$2:$A$10)-ROW($A$2)+1)))),1)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[bujabuja]

	A	B	C	D	E	F	G	H	I	J
1	Item	Class	Start	End			Lookup	Class	Location
2	Item1	A	30	80				A	70
3	Item2	A	40	150
4	Item3	A	60	160			Output
5	Item4	A	150	250			Item2	30	80	2400
6	Item5	B	300	400			Item3	10	90	900
7	Item6	B	400	500			Item1	40	10	400
8	Item7	C	50	300
9	Item8	C	70	150
10	Item9	C	150	300

<tbody>
</tbody>

​

Hi Eric,

thank you very much for your reply. I changed the numbers slightly.

The desired output would be:
Item2
Item3
Item1

I added the calculations for clarification, but these should not appear in the final version. The output is ordered by (Location-Start)*(End-Location).

I looked at your formula. I see that you cascaded the if statement and you added the countif-function. I don't quite understand what the countif function does here. Please explain.

Thank You.

 

[Eric W]

I think we're getting closer to your ultimate goal, but I'm a bit confused by your example. Item2 in the table on the left has Start and End of 40/150, but in the response table on the right it has 30/80?

But take a look at this:

	A	B	C	D	E	F	G	H	I	J	K
1	Item	Class	Start	End			Lookup	Class	Location
2	Item1	A	30	80				A	70
3	Item2	A	40	150
4	Item3	A	60	160			Output
5	Item4	A	150	250			Item3	60	160	9600
6	Item5	B	300	400			Item2	40	150	6000
7	Item6	B	400	500			Item1	30	80	2400
8	Item7	C	50	300			0	0	0
9	Item8	C	70	150
10	Item9	C	150	300

<tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula H5 =SUMIFS(C$2:C$10,$A$2:$A$10,$G5,$B$2:$B$10,$H$2) J5 =H5*I5 <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G5 {=IFERROR(INDEX($A$2:$A$10,MATCH(LARGE(IF(($B$2:$B$10=$H$2)*($C$2:$C$10<=$I$2)*($D$2:$D$10>=$I$2)*(COUNTIF($G$4:$G4,$A$2:$A$10)=0),$C$2:$C$10*$D$2:$D$10),1),IF(($B$2:$B10=$H$2)*($C$2:$C$10<=$I$2)*($D$2:$D$10>=$I$2)*(COUNTIF($G4:$G4,$A$2:$A$10)=0),$C$2:$C$10*$D$2:$D$10),0)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The main formula is in G5. The H and I formulas just return the matching locations, and J is just the product. You can remove any of those.

The formula mainly finds the matching rows, calculates C*D, and finds the largest. It then uses MATCH to find the location of the largest value within the matching rows again. The COUNTIF looks at the rows above the formula (so G5 looks at G4, G6 looks at G4:G5, G7 looks at G4:G6, etc.) to see if the item has already been found. If it has, it's excluded from further consideration, meaning that the next row down will find the next highest value, or the highest remaining after the highest is excluded.