Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort of VLOOKUP
I've got some data and I need something like vlookup. I've got a spreadsheet that I can't change which has the following info: Col A --- Col B area ---- plunger diameter 0.01 ---- 60 0.022 --- 120 0.072 --- 400 0.18 ---- 1000 0.45 ---- 2500 0.72 ---- 4000 I need to return a value for the size of plunger needed given a certain area. However, vlookup returns the value on the same row, i.e. if I have an area of 0.015 it returns 60. What I actually need is that for up to 0.01 it returns 60, then for up to 0.022 it returns 120 etc. I'm sure there's a really simple way of doing it. Currently the only way I've found to do it is a load of nested IF statements (and if the data needs extending this can get messy). Is there any way of using vlookup or something similar. Thanks in advance. Rob -- Rob_T ------------------------------------------------------------------------ Rob_T's Profile: http://www.excelforum.com/member.php...fo&userid=4952 View this thread: http://www.excelforum.com/showthread...hreadid=540587 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort of VLOOKUP
Use this table:
area plunger diameter 0,000 60 0,010 120 0,022 400 0,072 1000 0,180 2500 0,450 4000 0,720 Regards, Stefi €˛Rob_T€¯ ezt Ć*rta: I've got some data and I need something like vlookup. I've got a spreadsheet that I can't change which has the following info: Col A --- Col B area ---- plunger diameter 0.01 ---- 60 0.022 --- 120 0.072 --- 400 0.18 ---- 1000 0.45 ---- 2500 0.72 ---- 4000 I need to return a value for the size of plunger needed given a certain area. However, vlookup returns the value on the same row, i.e. if I have an area of 0.015 it returns 60. What I actually need is that for up to 0.01 it returns 60, then for up to 0.022 it returns 120 etc. I'm sure there's a really simple way of doing it. Currently the only way I've found to do it is a load of nested IF statements (and if the data needs extending this can get messy). Is there any way of using vlookup or something similar. Thanks in advance. Rob -- Rob_T ------------------------------------------------------------------------ Rob_T's Profile: http://www.excelforum.com/member.php...fo&userid=4952 View this thread: http://www.excelforum.com/showthread...hreadid=540587 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort of VLOOKUP
Unfortunately I can't change the spreadsheet the data is coming from, and it's liable to change so I can't just copy the data, I need to link to it. So I need to work with the data in its existing format. Any more suggestions out there? Rob -- Rob_T ------------------------------------------------------------------------ Rob_T's Profile: http://www.excelforum.com/member.php...fo&userid=4952 View this thread: http://www.excelforum.com/showthread...hreadid=540587 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort of VLOOKUP
With sorted data, you can use vlookup with the True option instead of False.
Have a look in Help for the difference between the two options. -- Damon Longworth 2006 UK Excel User Conference July 19/21st, 2006 University of Westminster - Marylebone Campus London, England Registration Now Open!! http://www.exceluserconference.com/2006UKEUC.html 2006 West Coast Excel User Conference October 2006 Tentative location - Los Angeles Metro area Announcement soon! http://www.exceluserconference.com/2006ECEUC.html "Rob_T" wrote in message ... I've got some data and I need something like vlookup. I've got a spreadsheet that I can't change which has the following info: Col A --- Col B area ---- plunger diameter 0.01 ---- 60 0.022 --- 120 0.072 --- 400 0.18 ---- 1000 0.45 ---- 2500 0.72 ---- 4000 I need to return a value for the size of plunger needed given a certain area. However, vlookup returns the value on the same row, i.e. if I have an area of 0.015 it returns 60. What I actually need is that for up to 0.01 it returns 60, then for up to 0.022 it returns 120 etc. I'm sure there's a really simple way of doing it. Currently the only way I've found to do it is a load of nested IF statements (and if the data needs extending this can get messy). Is there any way of using vlookup or something similar. Thanks in advance. Rob -- Rob_T ------------------------------------------------------------------------ Rob_T's Profile: http://www.excelforum.com/member.php...fo&userid=4952 View this thread: http://www.excelforum.com/showthread...hreadid=540587 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort of VLOOKUP
Damon Longworth Wrote: With sorted data, you can use vlookup with the True option instead of False. Have a look in Help for the difference between the two options. -- Damon Longworth Yes, with false it has to be an exact match. With true it uses the "nearest" value. Which actually means it carries on using one value until it comes to the next one (i.e. in this case if my area is 0.015 it returns 60). I need it to return the next hightest value once it's gone past the change (i.e. in this case, for area of 0.015 I need it to return 120). I was hoping that there might be a similar sort of formua or a different way of using vlookup to do what I want. Any more ideas? Cheers, Rob -- Rob_T ------------------------------------------------------------------------ Rob_T's Profile: http://www.excelforum.com/member.php...fo&userid=4952 View this thread: http://www.excelforum.com/showthread...hreadid=540587 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort of VLOOKUP
You could sort the data in descending order.
-- Damon Longworth 2006 UK Excel User Conference July 19/21st, 2006 University of Westminster - Marylebone Campus London, England Registration Now Open!! http://www.exceluserconference.com/2006UKEUC.html 2006 West Coast Excel User Conference October 2006 Tentative location - Los Angeles Metro area Announcement soon! http://www.exceluserconference.com/2006ECEUC.html "Rob_T" wrote in message ... Damon Longworth Wrote: With sorted data, you can use vlookup with the True option instead of False. Have a look in Help for the difference between the two options. -- Damon Longworth Yes, with false it has to be an exact match. With true it uses the "nearest" value. Which actually means it carries on using one value until it comes to the next one (i.e. in this case if my area is 0.015 it returns 60). I need it to return the next hightest value once it's gone past the change (i.e. in this case, for area of 0.015 I need it to return 120). I was hoping that there might be a similar sort of formua or a different way of using vlookup to do what I want. Any more ideas? Cheers, Rob -- Rob_T ------------------------------------------------------------------------ Rob_T's Profile: http://www.excelforum.com/member.php...fo&userid=4952 View this thread: http://www.excelforum.com/showthread...hreadid=540587 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort of VLOOKUP
What about this one (F2 contains the area value to be looked up):
=INDEX(B$2:B$7,IF(F2-0.001<0.01,1,MATCH(F2-0.001,A$2:A$7)+1)) Regards, Stefi €˛Rob_T€¯ ezt Ć*rta: Unfortunately I can't change the spreadsheet the data is coming from, and it's liable to change so I can't just copy the data, I need to link to it. So I need to work with the data in its existing format. Any more suggestions out there? Rob -- Rob_T ------------------------------------------------------------------------ Rob_T's Profile: http://www.excelforum.com/member.php...fo&userid=4952 View this thread: http://www.excelforum.com/showthread...hreadid=540587 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP 1 stop Answer: 3 steps: count dup's, compare dif's, merg | Excel Discussion (Misc queries) | |||
How can I sort multiple times | Excel Worksheet Functions | |||
Vlookup, Sort ?? | Excel Discussion (Misc queries) | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |