| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find closest match and return next highest number in range
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 !! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find closest match and return next highest number in range
Maybe
=INDEX(A1:A9,MATCH(365,A1:A9,1)+1) Mike "x6v87qe" wrote: 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 !! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find closest match and return next highest number in range
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 ! "Mike H" wrote: Maybe =INDEX(A1:A9,MATCH(365,A1:A9,1)+1) Mike "x6v87qe" wrote: 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 !! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find closest match and return next highest number in range
Hmmm,
Any more surprises? try this =IF(INDEX(A1:A9,MATCH(365,A1:A9,1))=365,INDEX(A1:A 9,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 "x6v87qe" wrote: 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 ! "Mike H" wrote: Maybe =INDEX(A1:A9,MATCH(365,A1:A9,1)+1) Mike "x6v87qe" wrote: 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 !! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find closest match and return next highest number in range
Ahhh!!!
No more triks!! Thanks Mike !! exactly what I'm after "Mike H" wrote: Hmmm, Any more surprises? try this =IF(INDEX(A1:A9,MATCH(365,A1:A9,1))=365,INDEX(A1:A 9,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 "x6v87qe" wrote: 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 ! "Mike H" wrote: Maybe =INDEX(A1:A9,MATCH(365,A1:A9,1)+1) Mike "x6v87qe" wrote: 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 !! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help! Find Closest Coordinate Match | Excel Discussion (Misc queries) | |||
| return next highest number in range | Excel Worksheet Functions | |||
| Find closest match and copy | Excel Discussion (Misc queries) | |||
| Find the closest match to a reference number in a row of unsorted | Excel Worksheet Functions | |||
| find closest match to a reference number in a row of numbers | Excel Discussion (Misc queries) | |||
Linear Mode
