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

[InvictusSol]

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.

 

[Mathias Pedersen Heinze]

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.

=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.

 

[mole999]

admit to being confused

Excel Workbook
	A	B
1	1.50	2
2	2.00	3
3	2.50	4
4	3.00	5
5	3.50	6
6	4.00	7
7	4.50	8
8	5.00	9
9	5.50	10
10	6.00	11
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]

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.

 

[InvictusSol]

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.

 

[Mathias Pedersen Heinze]

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.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.

 

[InvictusSol]

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.

 

[Mathias Pedersen Heinze]

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]

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]

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.

=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.