VLOOKUP/HLOOKUP Question

[TheRobsterUK]

Hi,

I am trying to get Excel to look up a certain value from a small table of values and have tried using the VLOOKUP/HLOOKUP functions but can't 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 Tank Size" and then Excel is supposed to look up the nearest but next largest value from the "Range of Tank Sizes Available" table.

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

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

Cheers
-Rob

[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

Mangesh

[TheRobsterUK]

Sounds promising but I failed to mention that the tank sizes in the "Range of Tank Sizes to Simulate" table can be changed by the user, so 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 HLOOKUP function to find the next smallest value, but then tell Excel to look one column to the right which (I think) should give the answer I want. I don't know how I would code for this though.......

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

Cheers
-Rob

[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
-Rob

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

[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


On Wed, 25 May 2005 07:29:40 -0500, TheRobsterUK
<[email protected]> wrote:
>
>Hi,
>
>I am trying to get Excel to look up a certain value from a small table
>of values and have tried using the VLOOKUP/HLOOKUP functions but can't
>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 Tank
>Size" and then Excel is supposed to look up the nearest but next
>-largest- value from the "Range of Tank Sizes Available" table.
>
>The problem I am having is that the VLOOKUP/HLOOKUP function only
>returns the next -smallest- value. This seems to be a hard coded
>feature and I can't change it. I also tried listing the tank sizes in
>reverse (descending) order but this just produces and error.
>
>Can anyone think of a way to get Excel to look up the next largest
>figure from the data table rather than the nearest smallest?
>
>Cheers
>-Rob

[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!