look for a set of values and then return the closest lower in this set

[vlbv]

Hello everyone,
I need the excel to return the lower closest value on a vlookup (?) result, that corresponds to a set of values, and I can't figure out how to build this formula.
Let me try to explain a little better:

So, I need the excel to return all the values that match "orange" (so, 20, 60, 10 and 50). And then, within these values, I need the excel to return the closest lower to 45 (so I need it to return "20").
Also... I have an enterprise account and I cannot enable Macros...
Can someone help, please...?

 

[steve the fish]

Hi try this:

=MAXIFS(B:B,A:A,$E$1,B:B,"<"&$H$3)

Obviously alter ranges to match your sheet.

 

[vlbv]

Hi try this:

=MAXIFS(B:B,A:A,$E$1,B:B,"<"&$H$3)

Obviously alter ranges to match your sheet.

Thank you for your help! Unfortunately, I don't have this formula available on my office version (I have the 2016 version).
I have tried a max(if(...(if...) function, but I must be doing something wrong, because I can't make it work! This is what I've tried: =MAX(IF(E1=A2:A10;IF(B2<H3;0);0)) it returns 0

Can you help, please?

 

[Fluff]

How about

ZFluff.xlsm
	A	B	C	D	E
1	Colour	Value		Colour	Orange
2	Orange	20		Target	45
3	Black	50		Result	20
4	Orange	60
5	Orange	10
6	Yellow	30
7	White	20
8	Pink	40
9	Orange	50
10	White	30
Data

Cell Formulas
Range	Formula
E3	E3	=AGGREGATE(14,6,B2:B10/((A2:A10=E1)*(B2:B10<=E2)),1)

 

[vlbv]

How about

ZFluff.xlsm
	A	B	C	D	E
1	Colour	Value		Colour	Orange
2	Orange	20		Target	45
3	Black	50		Result	20
4	Orange	60
5	Orange	10
6	Yellow	30
7	White	20
8	Pink	40
9	Orange	50
10	White	30
Data

Cell Formulas
Range	Formula
E3	E3	=AGGREGATE(14,6,B2:B10/((A2:A10=E1)*(B2:B10<=E2)),1)

Thank you so much!! I think this is working! Now I just have to complete with a condition for: if my "target" is lower than the values in column B, than it should assume the lowest value in this column. I'll try to get there alone, but I will most certainly accept help!

 

[Fluff]

You're welcome & thanks for the feedback