(Help with) Fairly easy formula - MIN ROW / HLOOKUP ?

InvictusSol

New Member
Joined
Jan 5, 2016
Messages
15
A little embarrassed to ask for help on this one, but after nearly an hour I'm stuck!


Have two columns of (ordered, low to high) numeric data. Eg:

A1 to A10 = 1.50, 2.00, 2.50, 3.00, 3.50, 4.00, 4.50, 5.00, 5.50, 6.00
B1 to B10 = 2, 3, 4, 5, 6, 7, 8, 9, 10, 11

For A1 (1.50), I want to search Column B and return the first value greater than A1 (2) - from cell B2.
For A4 (3.00), I want to search Column B and return the first value greater than A4 (4) - from cell B3.


I've got a feeling there's in {ARRAY} in there somewhere, maybe a MIN(ROW(.... or even a HLOOKUP(...

I know I'm close and will hate myself when somebody solves this in 30secs but as I said, have been stuck for too long on it myself!


Thanks.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Joined
Jul 7, 2015
Messages
44
Hi, you were definitely on the right track. Place the following formula in C1, close it with Ctrl + Shift + Enter (array formula) and drag it down.

Code:
=INDEX($B$1:$B$10,MIN(IF($B$1:$B$10>A1,ROW($B$1:$B$10),"")))

For the lookup value in A1 it will return 2 (from B1), and for the lookup value in A4 it will return 4 (from B3).

I hope this helps.
 
Last edited:

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
admit to being confused

Excel Workbook
AB
11.502
22.003
32.504
43.005
53.506
64.007
74.508
85.009
95.5010
106.0011
Sheet3


your lookup is max six yet 11 would seem to be a target and I can't see how 3 in A4 works to 4 in B3 and not three

a vlookup is someway to go, but dosen't do it, maybe CEILING just on A might be forced to work as you need
 

InvictusSol

New Member
Joined
Jan 5, 2016
Messages
15
Thanks for the reply - unfortunately that formula doesn't bring back the correct results. I have never used the INDEX function, so there's something learnt there!

Just the syntax you provided has jogged my memory a bit so thanks for that, I'll keep trying.

If anybody else is reading, all I want my formula to do is "for any cell in Column A, check all cells in Column B and return me the first instance of a cell greater in value than the cell in Column A". Both columns are sorted low-high.
 
Last edited:

InvictusSol

New Member
Joined
Jan 5, 2016
Messages
15

ADVERTISEMENT

Sorry, please don't get too stuck on the actual data values I set out in my example.

In reality, cells in Column A rise in value uniformly by 0.10 (2.10, 2.20, 2.30...). Cells in Column B rise is value first by 0.01, then 0.02, then 0.05, then 0.10, then 0.20 etc.
 
Last edited:
Joined
Jul 7, 2015
Messages
44
Thanks for the reply - unfortunately that formula doesn't bring back the correct results. I have never used the INDEX function, so there's something learnt there!

Just the syntax you provided has jogged my memory a bit so thanks for that, I'll keep trying.

If anybody else is reading, all I want my formula to do is "for any cell in Column A, check all cells in Column B and return me the first instance of a cell greater in value than the cell in Column A". Both columns are sorted low-high.

Please be more specific. What does the formula return exactly? Did you remember to close it using Ctrl + Shift + Enter? Here is what the formula returns in my sheet:

1.5022
2.0033
2.5043
3.0054
3.5064
4.0075
4.5085
5.0096
5.50106
6.00117

<tbody>
</tbody>

The third column being column C and the return column.
 

InvictusSol

New Member
Joined
Jan 5, 2016
Messages
15

ADVERTISEMENT

Please be more specific. What does the formula return exactly? Did you remember to close it using Ctrl + Shift + Enter? Here is what the formula returns in my sheet:

1.50
2
2
2.00
3
3
2.50
4
3
3.00
5
4
3.50
6
4
4.00
7
5
4.50
8
5
5.00
9
6
5.50
10
6
6.00
11
7

<tbody>
</tbody>

The third column being column C and the return column.


Everything there looks perfect. I'll try your solution again with a smaller dataset. Thanks again.
 
Joined
Jul 7, 2015
Messages
44
Sorry, please don't get too stuck on the actual data values I set out in my example.

In reality, cells in Column A rise in value uniformly by 0.10 (2.10, 2.20, 2.30...). Cells in Column B rise is value first by 0.01, then 0.02, then 0.05, then 0.10, then 0.20 etc.

With the formula I gave you, it doesn't really matter how the values in columns A and B increment, as long as they are sortet as you mentioned.
 

InvictusSol

New Member
Joined
Jan 5, 2016
Messages
15
With the formula I gave you, it doesn't really matter how the values in columns A and B increment, as long as they are sortet as you mentioned.

I have played around with it and your formula works correctly if the ranges start at Row1. I had previously had Headers and blank rows and data started at Row5 - this seemed to throw the INDEX formula out a bit?

For instance, starting at A5, Column A read 1.20, 1.30, 1.40, 1.50, 1.60. B5 down read 1.20, 1.21, 1.22, 1.23, 1.24.

The returned value for 1.20 was 1.25 (should've been 1.21) 1.30 was 1.35 (needed 1.31) etc. As soon as deleted upper rows and start from A1 - everything's perfect!
 
Joined
Jul 7, 2015
Messages
44
I have played around with it and your formula works correctly if the ranges start at Row1.

Yes, you are right about that. However, with this minor adjustment, you can move the formula around as you like; it doesn't have to start in row 1, it just have to start in the same row as the data.

Code:
=INDEX($B$1:$B$10,MIN(IF($B$1:$B$10>A1,ROW($B$1:$B$10),""))-ROW($A$1)+1)

Remember, you still have to close it with Ctrl + Shift + Enter.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,368
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top