VLOOKUP/HLOOKUP Question

[TheRobsterUK]

Hi,

I am trying to get Excel to look up a certain value from a small tabl
of values and have tried using the VLOOKUP/HLOOKUP functions but can'
get them to do exactly what I want. Here is how the spreadsheet looks:

Recommended Tank Size: 4400 litres

Range of Tank Sizes Available (litres):
1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000

Basically their is an algorithm which calculates the "Recommended Tan
Size" and then Excel is supposed to look up the nearest but nex
-largest- value from the "Range of Tank Sizes Available" table.

The problem I am having is that the VLOOKUP/HLOOKUP function onl
returns the next -smallest- value. This seems to be a hard code
feature and I can't change it. I also tried listing the tank sizes i
reverse (descending) order but this just produces and error.

Can anyone think of a way to get Excel to look up the next larges
figure from the data table rather than the nearest smallest?

Cheers
-Ro

 

[mangesh_yadav]

use something like:

=HLOOKUP(ROUNDUP(4400/1000,0)*1000,A1:J2,2)

Roundup your 4400 to 5000, which can be done by:
ROUNDUP(4400/1000,0)*1000

and then lookup

Manges

 

[TheRobsterUK]

Sounds promising but I failed to mention that the tank sizes in th
"Range of Tank Sizes to Simulate" table can be changed by the user, s
the values I posted aren't static.

Would this affect the method you suggested?

*Edit*

Actually, one approach I thought might work is using the HLOOKU
function to find the next smallest value, but then tell Excel to loo
one column to the -right- which (I think) should give the answer
want. I don't know how I would code for this though.......

Maybe use the OFFSET function somehow? E.g. get the cell reference tha
the HLOOKUP function refers to, then OFFSET one cell to the right an
get that value instead?

Cheers
-Ro

 

[Domenic]

First, sort your table in descending order. Then, assuming that A1:J2
contains your table, try...

=INDEX(A2:J2,MATCH(A5,A1:J1,-1))

...where A5 contains your lookup vallue.

Hope this helps!

 

[TheRobsterUK]

Dominic,

That great! Seems to do just what I need.

I take it that it wouldn't work if I input the numbers in -ascending
order and that they always have to be descending?

Cheers
-Ro

 

[Domenic]

With your values in ascending order, try...

=INDEX(A2:J2,IF(A5<A1,1,IF(A5>J1,#N/A,MATCH(A5,A1:J1)+(1-ISNUMBER(MATCH(A5,A1:J1,0))))))

...which should give you the same results.

Hope this helps

 

[TheRobsterUK]

Crikey! I think I'll just stick with the first one thanks!

I'll just put in a check routine to make sure thay have been entered in
descending order and if not just display a message stating that they
need to do this.

 

[Domenic]

Crikey! I think I'll just stick with the first one thanks!...

I don't blame you. :

 

[BobT]

Hey Rob

Assume Recommended Tank Size is in A2
and Range of Tank Sizes Available is in A1 to J1
In A3 put
=MIN(IF(A1:J1>A2,A1:J1))
This in array formula, so commit with Ctrl+Shift+Enter
which will place {} brackets around the formula.
This will give the minimum value greater than recommended regardless
of the order the available tanks are entered

You won't be able to use this as part of a larger formula, so you will
have to A3 if further calculation is needed

Bob

 

[TheRobsterUK]

Cheers for the replies everyone.

 

[TheRobsterUK]

Assume Recommended Tank Size is in A2
and Range of Tank Sizes Available is in A1 to J1
In A3 put
=MIN(IF(A1:J1>A2,A1:J1))
This in array formula, so commit with Ctrl+Shift+Enter
which will place {} brackets around the formula.
This will give the minimum value greater than recommended regardless
of the order the available tanks are entered

Actually could someone explain how this works? I've not really used
array formulas before but it looks as though they are very useful.

I think this is how it works:

1) Look at all the values in the rang A1:J1 and compare them to the
value in A2
2) Flag those values in the range A1:J1 that are greater than A2
3) Then report the -minimum- value from those flagged, which will give
the value which is the closest (but still greater) to that in A2

The bit I don't understand is the final expression: ,A1:J1. I
understand that as part of an IF function there needs to be a -value if
false- term at the end of it, so I assume that this is what this is
for.

What does it do though? Is it just there to complete the IF function or
is an essential part of how the formula does what I need it to do?


Cheers
-Rob

 

[Domenic]

Assuming that A1:E1 contains the following array of values...

{100,200,300,400,500,600}

...and that we have the following formula...

=MIN(IF(A1:E1>=A5,A1:E1))

...where A5 contains 350, then...

IF(A1:E1>=A5,A1:E1) returns the following array of values...

{FALSE,FALSE,FALSE,400,500}

In turn, the MIN function returns the minimum value, which in this case
is 400. If you want to return it's corresponding value in the second
row, then you can use the following formula...

=INDEX(A2:E2,MATCH(MIN(IF(A1:E1>=A5,A1:E1)),A1:E1,0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!