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

#### InvictusSol

##### New Member
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Mathias Pedersen Heinze

##### New Member
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

##### Well-known Member
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
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
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:

#### Mathias Pedersen Heinze

##### New Member
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.5 2 2 2 3 3 2.5 4 3 3 5 4 3.5 6 4 4 7 5 4.5 8 5 5 9 6 5.5 10 6 6 11 7

<tbody>
</tbody>

The third column being column C and the return column.

#### InvictusSol

##### New Member
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.5 2 2 2 3 3 2.5 4 3 3 5 4 3.5 6 4 4 7 5 4.5 8 5 5 9 6 5.5 10 6 6 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.

#### Mathias Pedersen Heinze

##### New Member
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
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!

#### Mathias Pedersen Heinze

##### New Member
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:

Replies
3
Views
723
Replies
3
Views
230
Replies
7
Views
377
Replies
1
Views
60
Replies
7
Views
458

### Forum statistics

1,171,686
Messages
5,876,895
Members
433,217
Latest member
Muhammad Tanzeel Ur Rehma

### 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?

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
{"found_math": false, "script_math_tex": 0, "script_math_asciimath": 0, "math_annotations": 0, "math_alttext": 0, "mathml": 0, "mathjax_tag": 0, "mathjax_inline_tex": 0, "mathjax_display_tex": 0, "mathjax_asciimath": 0, "img_math": 0, "codecogs_latex": 0, "wp_latex": 0, "mimetex.cgi": 0, "/images/math/codecogs": 0, "mathtex.cgi": 0, "katex": 0, "math-container": 0, "wp-katex-eq": 0, "align": 0, "equation": 0, "x-ck12": 0, "texerror": 0}