Scrolling down a list to find a specific number

[dandedo2]

Hi I have a problem at work that I am currently stuck on.

I have attached a simplified version of my spreadsheet with just the key numbers on to add colour to my description.

I have a list of numbers, column b in my sheet, and then another list of numbers, column f, and an output cell, column q. I want in the output cell (q8 in the example) it to display the next number in column f that is greater than the value in b8.

I've tried using if then else but am not having much joy. I can see that it should be fairly straightforward, and would be very much obliged if someone could help me out on this one.

If I could get the code for this function, I can then integrate it into my sheet for more functions.

Thanks

[GTO]

If I am understanding correctly, there's probably a more efficient formula based answer, but here's a stab at it. Presuming that the actual values are integers as shown, with the unsorted values in B5:B15, maybe a helper column.

To try:

In O5 :
=LARGE($B$5:$B$15,ROW()-4)
...and drag down thru O15.

In Q8 :
=INDEX($O$5:$O$15,MATCH(F8+1,$O$5:$O$15,-1),0)

Does that do what you want?

Mark

[mdmackillop]

in Q5 as an Array Formula and copy down
=MIN(IF((Secondary-B5)*((Secondary-B5)>0),(Secondary)))
where Secondary is the range F5:F15

[dandedo2]

Hey guys, thanks for your help. They're both producing results, but not the right ones if that makes sense.

I don't think I did a very good job of articulating my problem initially.

I'm trying to return in column Q, the value of the next value in column F that is in excess of the cell in column B corresponing to the same row as Q.

i.e. for the initial example sheet, in Q7 there would be the value of the next number in column F that is greater than B7 in order of rows descending. So the expected value would be 95.

[GTO]

Again, we'll get shown better, but practice makes perfect, so another shot.

In Q5, entered as an array formula, drag down.


=IF(ISERROR(INDEX($F5:$F$15,MATCH(TRUE,IF($F5:$F$15>$B5,TRUE),0))),"", _
INDEX($F5:$F$15,MATCH(TRUE,IF($F5:$F$15>$B5,TRUE),0)))

Please ditch the spaces and underscore before INDEX. I do not know a better way to keep the post from "stretching" with a long formula.

[dandedo2]

Still not producing the desired numbers. It's proving tricky. :dunno

I'll attach another spreadsheet which outlines it all much clearer than I have portrayed it I think.

I've annotated it to make it clearer.

All help is massively appreciated.

[GTO]

I'm afraid its off to bed for me (currently working nights).

I am not following evidently. In #4, you seem to state clearly that it should be looking for the first value that is greater in the second column. Yet you changed the formula to look for the same value?

Here is what I would think the formula should look like according to what I understand your wanting to return,


=IF(ISERROR(INDEX($F21:$F$33,MATCH(TRUE,IF($F21:$F$33>$B20,TRUE),0))), _
"",INDEX($F21:$F$33,MATCH(TRUE,IF($F21:$F$33>$B20,TRUE),0)))


Again, ditch the spaces and underscore.

Best of luck,

Mark

[dandedo2]

Cheers mark for your efforts, i feel it's getting closer!

So essentially the function is to scroll down the list to the point where Frow = B(row-1) if that makes sense. And then return that value into the cell in Q back by the original entry point.

[Aussiebear]

In post #1 you said... "I have a list of numbers, column b in my sheet, and then another list of numbers, column f, and an output cell, column q. I want in the output cell (q8 in the example) it to display the next number in column f that is greater than the value in b8.", which when applied to the workbook supplied in post#6, and using the rules quoted in there, B21 = 60 and there is no number greater in column F, so result is unachievable. I cannot follow where you arrived at "58".

Please attach a new workbook with a before and after worksheets, with a clear description of that which you are chasing. Otherwise we will simply be chasing our tails round the campfire.

[dandedo2]

I didn't accurately describe my problem in my first post it seems. The problem is that as described in the spreadsheet I added last with annotations. That explanation there when ignoring the first post is best. Sorry as I managed to confuse myself! Thank you