Find highest number in one column based on other criteria.

Gaffee

New Member
Joined
Jul 8, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
1740410675089.png


Hi all,

I've been playing for a while now and can't figure out if/how to get what I need. I want to find the highest possible number in column A, but from matching to othert criteria.

I want to input a defined number in cell AA3 which references B3:N3, and then input a number in cell AA5. This number will refer to B4:N21, I need it to be equal to or greater than (but be the closest to it) the number in cell AA5.

I then want to return the highest value in column A that meets these criteria.

eg.

If I put 31.2 in AA3, and 18 in AA5, I would want it to return the highest possible number in Column A which is 7 in this instance.

If you need any more clarity please ask, and thanks for any help.

Joe
 
I lost a lot of savviness for 2016. Someone here can do better than this. I changed the I9 to 18.7, because 18.6 was otherwise the lowest and returns a 4.

MrExcelPlayground24.xlsx
ABCDEFGHIJKLMNOPQ
1
2
310.310.313.817.320.724.227.731.234.638.141.645.14831.2
42.160
52.458.818
62.558.2
7354.642.38helper - could be integrated below
83.549.942.3
9444.540.518.718.7helper - could be integrated below
104.539.837.218.9
11536.434.219.17
12631.628.719.2
13726.524.118.7
14817.7
15915.9
161011.3
171112.6
181211.6
191310.5
20149.4
21158.5
Sheet4
Cell Formulas
RangeFormula
P7P7=MATCH(P3,B3:N3,0)
P9P9=MIN(IF(INDEX(B4:N21,,P7)<P5,9999999,INDEX(B4:N21,,P7)))
P11P11=INDEX(A1:A21,MAX((INDEX(B4:N21,,P7)=P9)*ROW(INDEX(B4:N21,,P7))))
 
Upvote 0

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