HLookup - next largest value

[samlim88]

I am having trouble returning the next highest value using HLOOKUP.

For example:


My table array (data) = 10, 20, 30, 40, 50


If my lookup value is 34, HLookup returns 30. I would like it to return 40 (round up instead of down).


Any help would be greatly appreciated

 

[oldchippy]

Hi,


Here's one way assuming your data is in A1:E1, your lookup value in G1=34 then this in H1

=INDEX(A1:E1,IF(ISNUMBER(MATCH(G1,A1:E1,0)),MATCH(G1,A1:E1,0),1+MATCH(G1,A1:E1,1)))

Result=40