Find Closest Upper value with multiple criterias

[Spartanjuli1]

Hello everyone,

I have a challenge for you
In the table below, I would like to have a formula (going from D5 to D19) which is giving me the closest upper "Total Hours" value when the C column is in state "TRUE".

Some formulas were tried, but it was always giving me B17 results instead of B14 cell.

• Do you have any ideas ??

	A	B	C	D
1	Total Years	Total Hours
2	3	3650
3
4	Total Years	Total Hours	"A5 <= $A$2 ?"	"Closest upper value $B$2"
5	2	1000	TRUE	IF A5 <= $A$2 THEN return closest upper value WHERE (B2 <= B5:B19)
6	2	1500	TRUE	FALSE
7	2	2000	TRUE	FALSE
8	2	2400	TRUE	FALSE
9	2	3000	TRUE	FALSE
10	3	1500	TRUE	FALSE
11	3	2250	TRUE	FALSE
12	3	3000	TRUE	FALSE
13	3	3600	TRUE	FALSE
14	3	4500	TRUE	TRUE (because 4500 is the closest upper value to B2)
15	4	2000	FALSE	FALSE
16	4	3000	FALSE	FALSE
17	4	3700	FALSE	FALSE
18	4	4800	FALSE	FALSE
19	4	6000	FALSE	FALSE

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Thank you a lot !
Julien

****** id="cke_pastebin" style="position: absolute; top: 318px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

IF A5 <= $A$2 THEN return closest upper value WHERE (B2 <= B5:B19)

<tbody>
</tbody>

</body>

 

[R0GU3]

Use Ctrl+Shift+Enter to make an array formula. Put code in any cell.

=MAX(IF(C5:C19,B5:B19,""))

 

[R0GU3]

If you wanted "True" or "False" all the way down Column D then:
Again Ctrl+Shift+Enter for array formula

{=IF(MAX(IF(C5:C19,B5:B19,""))=B5, "TRUE", "FALSE")}

 

[Spartanjuli1]

Hello,

Thank you for the answer, unfortunately I believe I was not maybe accurate enough:

Cells D5 to D19 should have the formula.

I want to have in cell D5 the value "TRUE" or "FALSE" when there are multiple criteria fulfilled:

#1 : A5<=&A&3 (A5 will be for cell D5, A6 for D6 until D19)
AND
#2 - when criteria #1 returning a value "TRUE", look up for the the closest Higher value from cells $B$5:$B$19.
At the end, if column A fulfils criteria, then look in the rows from column B where A rows fulfills the criteria and return the value where both conditions are met.


maybe with the real example from table above:
Using the table above, D14 should return ONLY "TRUE" value when A14 < $A$5 (3 <= 3) AND when B14 (4500) is the closest upper value to $B$2 (3650)
The trick here is that B17 (3700) value is the closest value to $B$2 (3650) but not A17 (4) as it is higher than $A$2 (3)

Results should look like the table above and I am a bit lost on that

Thank you !