Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
I have a question I was hoping someone could help me with.
I have column of numbers in ascending order, of about 20 or so rows. The numbers ascend sporadically ie, say column A1:A20 the numbers start … 1, 5, 13, 20, 25….. In the next column over (B1:B20), I have other numbers, that are random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76…. In Cell A25, I have an input number that is somewhere between the numbers in Column A. For example 12. I am trying to write a formula that will search the range in column A and will recognize the input of 12 is in between the 5 and 13 and then return the value in Column B that is on the same row of the 13. (ie the max value between the 5 and 13). I know there is a formula out there and I am trying to avoid VBA and also trying to avoid an array function-if possible I hope I have explained this correctly. Please let me know if I need to clarify. Thank you in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
On Aug 19, 1:04*pm, cardan wrote:
I have a question I was hoping someone could help me with. I have column of numbers in ascending order, of about 20 or so rows. The numbers ascend sporadically ie, say column A1:A20 *the numbers start … 1, 5, 13, 20, 25….. In the next column over (B1:B20), I have other numbers, that are random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76…. In Cell A25, I have an input number that is somewhere between the numbers in Column A. For example 12. *I am trying to write a formula that will search the range in column A and will recognize the input of 12 is in between the 5 and 13 and then return the value in Column B that is on the same row of the 13. (ie the max value between the 5 and 13). I know there is a formula out there and I am trying to avoid VBA and also trying to avoid an array function-if possible I hope I have explained this correctly. Please let me know if I need to clarify. Thank you in advance. This is a ARRAY formula that must be entered using ctrl+shift+enter vs just using enter =VLOOKUP(SMALL(IF($A$2:$A$8=C1,$A$2:$A$8),ROWS($A $2:A2)),A2:B22,2,0) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
Try this in B25:
=INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20)+1)) It can be copied across if you have other numbers in column C etc that you want to return. Hope this helps. Pete On Aug 19, 7:04*pm, cardan wrote: I have a question I was hoping someone could help me with. I have column of numbers in ascending order, of about 20 or so rows. The numbers ascend sporadically ie, say column A1:A20 *the numbers start … 1, 5, 13, 20, 25….. In the next column over (B1:B20), I have other numbers, that are random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76…. In Cell A25, I have an input number that is somewhere between the numbers in Column A. For example 12. *I am trying to write a formula that will search the range in column A and will recognize the input of 12 is in between the 5 and 13 and then return the value in Column B that is on the same row of the 13. (ie the max value between the 5 and 13). I know there is a formula out there and I am trying to avoid VBA and also trying to avoid an array function-if possible I hope I have explained this correctly. Please let me know if I need to clarify. Thank you in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
"Pete_UK" wrote:
Try this in B25: =INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20)+1)) I have not read Carl's requirements in detail to know if this is or is not a problem, but just an observation for consideration.... That formula will result in a #REF error if MATCH returns 20 -- that is, if A25 is greater than or equal to A20. ----- previous message ----- On Aug 19, 7:04 pm, cardan wrote: I have a question I was hoping someone could help me with. I have column of numbers in ascending order, of about 20 or so rows. The numbers ascend sporadically ie, say column A1:A20 the numbers start … 1, 5, 13, 20, 25….. In the next column over (B1:B20), I have other numbers, that are random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76…. In Cell A25, I have an input number that is somewhere between the numbers in Column A. For example 12. I am trying to write a formula that will search the range in column A and will recognize the input of 12 is in between the 5 and 13 and then return the value in Column B that is on the same row of the 13. (ie the max value between the 5 and 13). I know there is a formula out there and I am trying to avoid VBA and also trying to avoid an array function-if possible I hope I have explained this correctly. Please let me know if I need to clarify. Thank you in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
Hi Joe,
I thought about pointing that out, but the OP did say "... I have an input number that is somewhere between the numbers in Column A... ", so I took this to mean that the number in A25 would be less than the number in A20. If not, then it could be corrected with this: =IF($A25=$A$20,B$20,INDEX(B$1:B$20,MATCH($A25,$A$ 1:$A$20)+1))) and to be fully bullet-proof if the number might be less than that in A1: =IF($A25<$A$1,"too small",IF($A25=$A$20,"too large",INDEX(B$1:B $20,MATCH($A25,$A$1:$A$20)+1)))) although the OP could think about using data validation on A25 to force acceptable numbers. Hope this helps. Pete On Aug 19, 8:18*pm, "joeu2004" wrote: "Pete_UK" wrote: Try this in B25: =INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20)+1)) I have not read Carl's requirements in detail to know if this is or is not a problem, but just an observation for consideration.... That formula will result in a #REF error if MATCH returns 20 -- that is, if A25 is greater than or equal to A20. ----- previous message ----- On Aug 19, 7:04 pm, cardan wrote: I have a question I was hoping someone could help me with. I have column of numbers in ascending order, of about 20 or so rows. The numbers ascend sporadically ie, say column A1:A20 the numbers start … 1, 5, 13, 20, 25….. In the next column over (B1:B20), I have other numbers, that are random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76…. In Cell A25, I have an input number that is somewhere between the numbers in Column A. For example 12. I am trying to write a formula that will search the range in column A and will recognize the input of 12 is in between the 5 and 13 and then return the value in Column B that is on the same row of the 13. (ie the max value between the 5 and 13). I know there is a formula out there and I am trying to avoid VBA and also trying to avoid an array function-if possible I hope I have explained this correctly. Please let me know if I need to clarify. Thank you in advance.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
On Aug 19, 1:56*pm, Pete_UK wrote:
Try this in B25: =INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20)+1)) It can be copied across if you have other numbers in column C etc that you want to return. Hope this helps. Pete On Aug 19, 7:04*pm, cardan wrote: I have a question I was hoping someone could help me with. I have column of numbers in ascending order, of about 20 or so rows. The numbers ascend sporadically ie, say column A1:A20 *the numbers start … 1, 5, 13, 20, 25….. In the next column over (B1:B20), I have other numbers, that are random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76…. In Cell A25, I have an input number that is somewhere between the numbers in Column A. For example 12. *I am trying to write a formula that will search the range in column A and will recognize the input of 12 is in between the 5 and 13 and then return the value in Column B that is on the same row of the 13. (ie the max value between the 5 and 13). I know there is a formula out there and I am trying to avoid VBA and also trying to avoid an array function-if possible I hope I have explained this correctly. Please let me know if I need to clarify. Thank you in advance.- Hide quoted text - - Show quoted text - If you ask for 12 with this formula you will get the higher figure. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
On Fri, 19 Aug 2011 11:04:37 -0700 (PDT), cardan wrote:
I have a question I was hoping someone could help me with. I have column of numbers in ascending order, of about 20 or so rows. The numbers ascend sporadically ie, say column A1:A20 the numbers start … 1, 5, 13, 20, 25….. In the next column over (B1:B20), I have other numbers, that are random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76…. In Cell A25, I have an input number that is somewhere between the numbers in Column A. For example 12. I am trying to write a formula that will search the range in column A and will recognize the input of 12 is in between the 5 and 13 and then return the value in Column B that is on the same row of the 13. (ie the max value between the 5 and 13). I know there is a formula out there and I am trying to avoid VBA and also trying to avoid an array function-if possible I hope I have explained this correctly. Please let me know if I need to clarify. Thank you in advance. The following is an array-formula, to be entered by holding down <CTRL<SHIFT while hitting <ENTER: =INDEX($B$1:$B$20,MATCH(TRUE,$A$25<=$A$1:$A$20,0)) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
Hi Don,
that's what the OP asked for. Pete On Aug 19, 10:10*pm, Don Guillett wrote: If you ask for 12 with this formula you will get the higher figure |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
"Pete_UK" wrote:
I thought about pointing that out, but the OP did say "... I have an input number that is somewhere between the numbers in Column A... ", so I took this to mean that the number in A25 would be less than the number in A20. Right. So when someone says "pick an integer between 1 and 10", you honestly believe that only the numbers 2 through 9 are fair game? Yeah, right. News flash: English is an ambiguous language. That's why careful people say "between this and that inclusively" or "between this and that exclusively". But most people are not that careful. In my experience, 98 times out of 100, when someone says "between x and y", they mean inclusively. YMMV. Pete wrote: and to be fully bullet-proof if the number might be less than that in A1: =IF($A25<$A$1,"too small", [....] The test for A25<A1 should not be necessary because "between" never includes numbers less than the lower limit and more than the greater limit. Of course, Carl might want to consider that if he has no control over data in A25; for example, if he is providing this to others for their use. But as you say: "although the OP could think about using data validation on A25 to force acceptable numbers". That still does not solve the ambiguity of the English word "between". ----- previous message ----- On Aug 19, 8:18 pm, "joeu2004" wrote: "Pete_UK" wrote: Try this in B25: =INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20)+1)) I have not read Carl's requirements in detail to know if this is or is not a problem, but just an observation for consideration.... That formula will result in a #REF error if MATCH returns 20 -- that is, if A25 is greater than or equal to A20. ----- previous message ----- On Aug 19, 7:04 pm, cardan wrote: I have a question I was hoping someone could help me with. I have column of numbers in ascending order, of about 20 or so rows. The numbers ascend sporadically ie, say column A1:A20 the numbers start … 1, 5, 13, 20, 25….. In the next column over (B1:B20), I have other numbers, that are random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76…. In Cell A25, I have an input number that is somewhere between the numbers in Column A. For example 12. I am trying to write a formula that will search the range in column A and will recognize the input of 12 is in between the 5 and 13 and then return the value in Column B that is on the same row of the 13. (ie the max value between the 5 and 13). I know there is a formula out there and I am trying to avoid VBA and also trying to avoid an array function-if possible I hope I have explained this correctly. Please let me know if I need to clarify. Thank you in advance.- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
On Aug 19, 7:46*pm, Pete_UK wrote:
Hi Don, that's what the OP asked for. Pete On Aug 19, 10:10*pm, Don Guillett wrote: If you ask for 12 with this formula you will get the higher figure- Hide quoted text - - Show quoted text - I MEANT (getting old) if the OP asked for 13 they would NOT get 13 but would get 20 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
On Aug 20, 8:37*am, Don Guillett wrote:
On Aug 19, 7:46*pm, Pete_UK wrote: Hi Don, that's what the OP asked for. Pete On Aug 19, 10:10*pm, Don Guillett wrote: If you ask for 12 with this formula you will get the higher figure- Hide quoted text - - Show quoted text - I MEANT (getting old) if the OP asked for 13 they would NOT get 13 but would get 20 Hello All, Thank you for the quick responses. I apologize if I started a heated debate on symantics and the use of the english language. I didn't think too much about the exactness of my post. I kinda of assumed I could add to a given solution with an equal sign. What I should clarify is this. For an example, if the input number is 5 and the range in Column A has the numbers, 1, 5, 13, etc... the formula should return the number in the corresponding range in Column B. (the number 6 in my original post.). If the input number is greater than 5 or equal to 13, (keeping with my original example again), then it should return the number in column B corresponding with the same row as the 13. I hope I am explaining this correctly. The Index Match, formula posted by Pete_UK works very well for finding and returning the maximum, however if my input number is five, it returns. the number corresponding to the 13. I would like it to return the number corresponding to the 5. I realize this was due to my ambuguity in my OP. I apologize again. Is there a way to get the formula to say if the input number is equal to a number in column A, return the number in column B in the same row, otherwise, if the input number is in between two numbers, return the number in column B that corresponds to the larger number in column A. I hope this is more clear. Again, Thank you for your time. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
Bearing in mind the earlier points about choosing numbers outside your
range, you can try this one: =IF($A25<$A$1,"too small",IF($A25$A$20,"too large",IF(ISNA(MATCH($A25,$A$1:$A$20,0)),INDEX(B$1 :B$20,MATCH($A25,$A $1:$A$20)+1),INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20, 0))))) If there is an exact match this will return the corresponding value, otherwise the next value, as long as the chosen number is within your range. Hope this helps. Pete On Aug 22, 5:29*pm, cardan wrote: Hello All, Thank you for the quick responses. *I apologize if I started a heated debate on symantics and the use of the english language. I didn't think too much about the exactness of my post. I kinda of assumed I could add to a given solution with an equal sign. What I should clarify is this. *For an example, if the input number is 5 and the range in Column A has the numbers, 1, 5, 13, etc... the formula should return the number in the corresponding range in Column B. (the number 6 in my original post.). If the input number is greater than 5 or equal to 13, (keeping with my original example again), then it should return the number in column B corresponding with the same row as the 13. *I hope I am explaining this correctly. The Index Match, formula posted by Pete_UK works very well for finding and returning the maximum, however if my input number is five, it returns. the number corresponding to the 13. *I would like it to return the number corresponding to the 5. *I realize this was due to my ambuguity in my OP. I apologize again. *Is there a way to get the formula to say if the input number is equal to a number in column A, return the number in column B in the same row, otherwise, if the input number is in between two numbers, return the number in column B that corresponds to the larger number in column A. *I hope this is more clear. *Again, Thank you for your time. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a Value Between Two Numbers and Returning Corresponding Value
On Mon, 22 Aug 2011 09:29:09 -0700 (PDT), cardan wrote:
On Aug 20, 8:37*am, Don Guillett wrote: On Aug 19, 7:46*pm, Pete_UK wrote: Hi Don, that's what the OP asked for. Pete On Aug 19, 10:10*pm, Don Guillett wrote: If you ask for 12 with this formula you will get the higher figure- Hide quoted text - - Show quoted text - I MEANT (getting old) if the OP asked for 13 they would NOT get 13 but would get 20 Hello All, Thank you for the quick responses. I apologize if I started a heated debate on symantics and the use of the english language. I didn't think too much about the exactness of my post. I kinda of assumed I could add to a given solution with an equal sign. What I should clarify is this. For an example, if the input number is 5 and the range in Column A has the numbers, 1, 5, 13, etc... the formula should return the number in the corresponding range in Column B. (the number 6 in my original post.). If the input number is greater than 5 or equal to 13, (keeping with my original example again), then it should return the number in column B corresponding with the same row as the 13. I hope I am explaining this correctly. The Index Match, formula posted by Pete_UK works very well for finding and returning the maximum, however if my input number is five, it returns. the number corresponding to the 13. I would like it to return the number corresponding to the 5. I realize this was due to my ambuguity in my OP. I apologize again. Is there a way to get the formula to say if the input number is equal to a number in column A, return the number in column B in the same row, otherwise, if the input number is in between two numbers, return the number in column B that corresponds to the larger number in column A. I hope this is more clear. Again, Thank you for your time. The array formula I posted three days ago will do exactly that: =INDEX($B$1:$B$20,MATCH(TRUE,$A$25<=$A$1:$A$20,0)) To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a value and returning corresponding info | Excel Discussion (Misc queries) | |||
Finding a cell and returning value from a different row and column | Excel Discussion (Misc queries) | |||
Finding an item in a list & returning a specific value | Excel Worksheet Functions | |||
Finding value and returning row number | Excel Programming | |||
Finding, and returning data. | New Users to Excel |