Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP FORMULA - USING THRESHOLDS - HELP
Trying to create a lookup using thresholds. Col B represents the upper
limit, Col C represents the return value. Using the table below: If I had a cell in Col A with the value of 8, I would expect a return value of 1 from Col C. If I had a cell in Col A with the value of 78, I would expect a return value of 3 from Col C. Col B Col C 18 1 54 2 90 3 126 4 162 5 198 6 234 7 270 8 306 9 342 10 378 11 414 12 450 13 486 14 522 15 558 16 594 17 Is there a lookup formula for this? Is there a better way of doing this? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP FORMULA - USING THRESHOLDS - HELP
Try: =INDEX($C$2:$C$18,MATCH(TRUE,INDEX($B$2:$B$18=A2, 0),0)) adjust ranges to suit. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116195 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP FORMULA - USING THRESHOLDS - HELP
Perfect! Thank you!
"NBVC" wrote: Try: =INDEX($C$2:$C$18,MATCH(TRUE,INDEX($B$2:$B$18=A2, 0),0)) adjust ranges to suit. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116195 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP FORMULA - USING THRESHOLDS - HELP
Note that with a slight change in your data layout you could have used a
much simpler formula: 0 1 18 2 54 3 90 4 etc. Your formula in that case: =VLOOKUP(A1,B1:C100,2) -- Kind regards, Niek Otten Microsoft MVP - Excel "txm49" wrote in message ... Perfect! Thank you! "NBVC" wrote: Try: =INDEX($C$2:$C$18,MATCH(TRUE,INDEX($B$2:$B$18=A2, 0),0)) adjust ranges to suit. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116195 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP FORMULA - USING THRESHOLDS - HELP
or even more simple: =LOOKUP(A1,B1:C100) Niek Otten;417753 Wrote: Note that with a slight change in your data layout you could have used a much simpler formula: 0 1 18 2 54 3 90 4 etc. Your formula in that case: =VLOOKUP(A1,B1:C100,2) -- Kind regards, Niek Otten Microsoft MVP - Excel "txm49" wrote in message ... Perfect! Thank you! "NBVC" wrote: Try: =INDEX($C$2:$C$18,MATCH(TRUE,INDEX($B$2:$B$18=A2, 0),0)) adjust ranges to suit. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC' (http://www.thecodecage.com/forumz/members/nbvc.html) View this thread: 'LOOKUP FORMULA - USING THRESHOLDS - HELP - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=116195) -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116195 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Thresholds | Charts and Charting in Excel | |||
Lookup formula - please help! | Excel Discussion (Misc queries) | |||
Create a Bullseye chart with value thresholds | Excel Discussion (Misc queries) | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions | |||
Negative Thresholds in SumProduct | Excel Discussion (Misc queries) |