Find Closest Upper value with multiple criterias

Spartanjuli1

New Member
Joined
Sep 13, 2016
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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 ??


[TABLE="class: grid, width: 519"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total Years[/TD]
[TD] Total Hours[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3650[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Total Years[/TD]
[TD]Total Hours[/TD]
[TD]"A5 <= $A$2 ?"[/TD]
[TD]"Closest upper value $B$2"[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]1000[/TD]
[TD]TRUE
[/TD]
[TD]IF A5 <= $A$2 THEN return closest upper value WHERE (B2 <= B5:B19)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]1500[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]2000[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]2400[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]3000[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]1500[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]3[/TD]
[TD]2250[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]3[/TD]
[TD]3000[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3[/TD]
[TD]3600[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]3[/TD]
[TD]4500[/TD]
[TD]TRUE[/TD]
[TD]TRUE (because 4500 is the closest upper value to B2)[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4[/TD]
[TD]2000[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4[/TD]
[TD]3000[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]4[/TD]
[TD]3700[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]4[/TD]
[TD]4800[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]4[/TD]
[TD]6000[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]


Thank you a lot !
Julien

****** id="cke_pastebin" style="position: absolute; top: 318px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 519"]
<tbody>[TR]
[TD]IF A5 <= $A$2 THEN return closest upper value WHERE (B2 <= B5:B19)[/TD]
[/TR]
</tbody>[/TABLE]
</body>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Use Ctrl+Shift+Enter to make an array formula. Put code in any cell.
Code:
=MAX(IF(C5:C19,B5:B19,""))
 
Upvote 0
If you wanted "True" or "False" all the way down Column D then:
Again Ctrl+Shift+Enter for array formula
Code:
{=IF(MAX(IF(C5:C19,B5:B19,""))=B5, "TRUE", "FALSE")}
 
Upvote 0
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 !
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top