Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that it will look at the number in column A and see if it is larger than the number in column B, if so pull the number in column C. The caveat is that I need it so if the number in Column C is less than the number in Column B, i need it to go to the next row then the next row until it is larger than the number in Column B
Solved by G. H. in 54 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
29/06/2018 - 02:13
Do i need to input an example?
Excelchat Expert
29/06/2018 - 02:14
Hello
Welcome to Gotit pro,we are always glad to help you.
May I ask how may I help you?
User
29/06/2018 - 02:14
i need help creating a fomrula
User
29/06/2018 - 02:14
formula*
Excelchat Expert
29/06/2018 - 02:14
ok
Excelchat Expert
29/06/2018 - 02:14
could i have your file so we can check on it?
User
29/06/2018 - 02:14
i cna set the example up
User
29/06/2018 - 02:14
i dont hav ea file
User
29/06/2018 - 02:15
its just an in theory
Excelchat Expert
29/06/2018 - 02:15
ok that much better :)
User
29/06/2018 - 02:15
so in column B
User
29/06/2018 - 02:15
i need a formula
User
29/06/2018 - 02:15
that looks at the number in column A
User
29/06/2018 - 02:16
and then sees which number it matches (or is lower) in column C
User
29/06/2018 - 02:16
and pulls column D
Excelchat Expert
29/06/2018 - 02:16
you want to look up the letter corresponding on number 5 right?
User
29/06/2018 - 02:16
sorta
User
29/06/2018 - 02:16
i need it to basically look up the letter
User
29/06/2018 - 02:16
correseponding to when its lower than column C
User
29/06/2018 - 02:16
so in this Cause it will be F
User
29/06/2018 - 02:16
case*
User
29/06/2018 - 02:17
so for the 8 it will be H
Excelchat Expert
29/06/2018 - 02:17
since there is no 5 on column C?
User
29/06/2018 - 02:17
yeah there is no 5 in column C
Excelchat Expert
29/06/2018 - 02:17
ok i get it :)
Excelchat Expert
29/06/2018 - 02:17
just give me a minute to work on this :)
User
29/06/2018 - 02:18
sure
User
29/06/2018 - 02:26
its a tough one isnt it haha
Excelchat Expert
29/06/2018 - 02:27
haha, I'm just near :)
Excelchat Expert
29/06/2018 - 02:27
yes it's quite difficult :)
User
29/06/2018 - 02:27
i was thinking would htis be an array formula
Excelchat Expert
29/06/2018 - 02:27
yes
User
29/06/2018 - 02:34
yeah so in this case that would be correct for the F
User
29/06/2018 - 02:34
but i would need for the 8 to pull H
Excelchat Expert
29/06/2018 - 02:34
yes,
Excelchat Expert
29/06/2018 - 02:34
I trying to work for some formula that will work for all numbers :)
User
29/06/2018 - 02:34
kk
Excelchat Expert
29/06/2018 - 02:35
:)
User
29/06/2018 - 02:35
take your time
User
29/06/2018 - 02:35
i was stumped on this for awhil
Excelchat Expert
29/06/2018 - 02:35
thanks :)
Excelchat Expert
29/06/2018 - 02:49
could you extend again the time? :)
Excelchat Expert
29/06/2018 - 02:50
i'm almost near on the solution :)
User
29/06/2018 - 02:50
sure
Excelchat Expert
29/06/2018 - 02:53
done for the formula :)
User
29/06/2018 - 02:53
may i alter the sitaution
User
29/06/2018 - 02:53
to see if it applicable
User
29/06/2018 - 02:54
in every sitaution
Excelchat Expert
29/06/2018 - 02:54
yes
User
29/06/2018 - 02:54
but this looks really good
Excelchat Expert
29/06/2018 - 02:54
you may try it :)
User
29/06/2018 - 02:55
this is awesome
Excelchat Expert
29/06/2018 - 02:55
great :)
Excelchat Expert
29/06/2018 - 02:55
the first formula that i used was the vlookup
Excelchat Expert
29/06/2018 - 02:55
but instead of the exact match, i changed it a approximately match
Excelchat Expert
29/06/2018 - 02:56
like this one
Excelchat Expert
29/06/2018 - 02:56
VLOOKUP(A6,C:C,1)
User
29/06/2018 - 02:56
i tried attempting that too
Excelchat Expert
29/06/2018 - 02:56
if I used that, the value would be lesser value right?
Excelchat Expert
29/06/2018 - 02:57
after i get the lower value, i used the formula match so i could get the column number of that lesser value
Excelchat Expert
29/06/2018 - 02:58
then after that when i have got the row number of the lesser value, I used the formula index
Excelchat Expert
29/06/2018 - 02:58
and then the row number that i get, i simply add 1 so i can get the next row number after the lesser value
User
29/06/2018 - 02:59
ohhhh
User
29/06/2018 - 02:59
out of curiosity
User
29/06/2018 - 02:59
how greatly would this formula change
User
29/06/2018 - 02:59
if all these numbers
User
29/06/2018 - 02:59
were negative?
Excelchat Expert
29/06/2018 - 02:59
you may right it also :)
User
29/06/2018 - 03:00
haha sorry i meant to say
User
29/06/2018 - 03:01
if it was changed into a negative aspect
User
29/06/2018 - 03:01
so like for -4
User
29/06/2018 - 03:01
i need it to pull D
User
29/06/2018 - 03:01
dont worry if this will take to olong
User
29/06/2018 - 03:01
i will take it from here if its not a quick qnaswer
Excelchat Expert
29/06/2018 - 03:01
ok wait, i will check :)
Excelchat Expert
29/06/2018 - 03:03
it looks, we need to revised all the formula if all the value would be negative
User
29/06/2018 - 03:04
but in general
User
29/06/2018 - 03:04
it should be the same formula correct?
User
29/06/2018 - 03:04
but now it just needs to pul
User
29/06/2018 - 03:04
the next smallest value?
Excelchat Expert
29/06/2018 - 03:04
yes, unfortunately vlookup formula doesn't work like that if all values are negative
User
29/06/2018 - 03:05
oh really?
User
29/06/2018 - 03:05
i didnt know vlookup had such a flaw
Excelchat Expert
29/06/2018 - 03:05
yes, you may check the formula i type in E6
Excelchat Expert
29/06/2018 - 03:06
instead of -3, it shows as -200000000
User
29/06/2018 - 03:06
yeah
User
29/06/2018 - 03:06
interesting
Excelchat Expert
29/06/2018 - 03:06
so if all values would be negative, we need to create another formula again
Excelchat Expert
29/06/2018 - 03:06
that will works for negative
User
29/06/2018 - 03:07
ahh okay, its fine, i will tkae it from this
User
29/06/2018 - 03:07
this formula alone was a good start for me
Excelchat Expert
29/06/2018 - 03:07
great :)
Excelchat Expert
29/06/2018 - 03:07
i'm glad I've helped you :)
User
29/06/2018 - 03:07
thanks!
Excelchat Expert
29/06/2018 - 03:07
Apart from this, is there anything else you want to know on your problem?
User
29/06/2018 - 03:07
no
User
29/06/2018 - 03:07
that was basically it
User
29/06/2018 - 03:07
thank you!
Excelchat Expert
29/06/2018 - 03:08
good :)
Excelchat Expert
29/06/2018 - 03:08
welcome :)
Excelchat Expert
29/06/2018 - 03:08
Thanks for using Got It Pro. Please give your kind feedback for our service.
You may click the End session to close our session properly and provide your kind feedback
Have a good day ahead ^_^
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.