Find closest match and return next highest number in range

X

x6v87qe

Hi, Group
I have tried doing this with INDEX and MATCH but without success.
I have 2 columns of data, A and B

A B

40 40
100 50
100 70
200 115
400 365
600 40
800 80
1200 985
1600 1150

What I need to do is take
the one value from column B (365 in this case) find the closest value in A
then return the next highest value from column A (600).

Grateful for any help !!
 
X

x6v87qe

Hi, Mike

Very close to what I need ! one more criteria: if the value taken out has a
exactly match then return that value. if not then return the the next highest
value

Thanks Mike !
 
M

Mike H

Hmmm,

Any more surprises? try this

=IF(INDEX(A1:A9,MATCH(365,A1:A9,1))=365,INDEX(A1:A9,MATCH(365,A1:A9,1)),INDEX(A1:A9,MATCH(365,A1:A9,1)+1))

As the formula is now quite long I would suggest you use a cell reference
instead of putting 365 in the formula which is good practice anyway.

=IF(INDEX(A1:A9,MATCH(C1,A1:A9,1))=C1,INDEX(A1:A9,MATCH(C1,A1:A9,1)),INDEX(A1:A9,MATCH(C1,A1:A9,1)+1))

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

return next highest number in range 9
averaging ranges 2
finding is a value is betwen a range of values 5
Formula help please 2
Find closest match and copy 3
Add a number '1' next to highest number in column 3
lookup and return column number 2
Find 2 values closest that are higher 2

Top