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


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=373860

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=373860

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


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=373860

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!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=373860

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


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=373860

With your values in ascending order, try...

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

...which should give you the same results.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=373860

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.


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=373860

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

I don't blame you. :)


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=373860

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:J1A2,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
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

Cheers for the replies everyone. :)


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=373860

Assume Recommended Tank Size is in A2
and Range of Tank Sizes Available is in A1 to J1
In A3 put
=MIN(IF(A1:J1A2,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


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=373860

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!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=373860