Find the First Number In a Table of Sorted Numbers That is Smaller Than Another Number

[Eb0la11]

Hey all, I have a calculated number that could be between 10-448 in Cell C1, I want to then compare that calculated number to a chart of numbers that are sorted smallest to largest and return the first number in that chart that is larger than my calculated number. Here is a picture of my chart of numbers it would look to. What would be a smooth formula to do that?

 

[Cubist]

Hi,
What is your current XL version? You should update your Account Details to let other members know.

 

[Cubist]

For D365, XLOOKUP should do the job. For prior versions, I think INDEX/MATCH accomplish the same thing. Adjust the range references as needed.

Book1
	A	B	C	D	E	F	G
1	Rows	Height	55		D365	OlderVersions
2	1	10			90	90
3	2	50
4	3	90
5	4	102
6	5	142
7	6	182
8	7	194
9	8	234
10	9	274
11
Sheet5

Cell Formulas
Range	Formula
E2	E2	=XLOOKUP(TRUE,B2:B10>C1,B2:B10)
F2	F2	=INDEX(B2:B10,MATCH(TRUE,B2:B10>C1,0))

 

[Peter_SSs]

A couple of other options depending on your excel version

=MINIFS(B3:B17,B3:B17,">"&C1)

=AGGREGATE(15,6,B3:B17/(B3:B17>C1),1)

 

[Eb0la11]

Thanks everyone, I updated my account details as well, we are running 365 and so I used the XLookup solution and think it will work. I'll plug that in and see if I have any issues, and if not I will try the others. Have a great weekend everyone!