Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP/HLOOKUP Question
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Question about combining data from multiple workbooks into one rep | Excel Discussion (Misc queries) | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
Compare Question | Excel Worksheet Functions | |||
Calculation Question | Excel Worksheet Functions |