Need excel formula to find highest number up to a certain number

mrnacar

Board Regular
Joined
Jan 27, 2010
Messages
188
Office Version
  1. 365
Platform
  1. Windows
Need excel formula to find highest number up to a certain number. For example, I want to find the highest number up to 26 on the following set of numbers in a single row.

15, 23, 25, 36, 38 Answer = 25
21, 22, 26, 42, 43 Answer = 26
10, 12, 15, 22, 27 Answer = 22
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try the following?
Book86
ABCDEF
2152325363825
3212226424326
4101215222722
Sheet2
Cell Formulas
RangeFormula
F2:F4F2=MAX(IF(A2:E2<=26,A2:E2,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Are you still using Excel 2007 per your signature?
I suggest that you update your Account details (or click your user name at the top right of the forum) & move your version information to there as helper will be looking here to what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

1690944294758.png


In case you have updated significantly, try this

23 08 02.xlsm
ABCDEF
1
2152325363825
3212226424326
4101215222722
Max
Cell Formulas
RangeFormula
F2:F4F2=MAXIFS(A2:E2,A2:E2,"<=26")
 
Upvote 0
Are you still using Excel 2007 per your signature?
I suggest that you update your Account details (or click your user name at the top right of the forum) & move your version information to there as helper will be looking here to what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

View attachment 96415

In case you have updated significantly, try this

23 08 02.xlsm
ABCDEF
1
2152325363825
3212226424326
4101215222722
Max
Cell Formulas
RangeFormula
F2:F4F2=MAXIFS(A2:E2,A2:E2,"<=26")
Thanks Peter! Been awhile since I posted here. Just updated my profile. Thanks again!
 
Upvote 0
Just updated my profile.
In that case you can either use the post #2 formula and you don't need to confirm it with Ctrl+Shift+Enter, or you can use the single-function MAXIFS formula post #3
 
Upvote 0
In that case you can either use the post #2 formula and you don't need to confirm it with Ctrl+Shift+Enter, or you can use the single-function MAXIFS formula post #3
Great! Good to have options! Thanks again!
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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