Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value associated with a range
Hello. I am attempting to create a formula that searches through a range
of values to identify where a number falls within the range and then, once identified, takes an associated value as a multiplier in the formula. Here is the range data: A B C D E 1 400 600 950 1250 1450 2 $15 $30 $45 $50 $55 Lookup value: 425 Based on these values, I need a formula to search a1 through e1 and return the value in B2 ($30) associated with 600 (because 425 is over 400, but not greater than 600) and allow me to then multiply the lookup value of 425 by the value returned (e.g., $30) giving me a result of $12,750. It's tough even describing what I want, but hopefully someone understands and can assist me ASAP. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value associated with a range
Look in HELP for the HLOOKUP() function
-- Kind regards, Niek Otten "Raymond Gallegos" wrote in message ... Hello. I am attempting to create a formula that searches through a range of values to identify where a number falls within the range and then, once identified, takes an associated value as a multiplier in the formula. Here is the range data: A B C D E 1 400 600 950 1250 1450 2 $15 $30 $45 $50 $55 Lookup value: 425 Based on these values, I need a formula to search a1 through e1 and return the value in B2 ($30) associated with 600 (because 425 is over 400, but not greater than 600) and allow me to then multiply the lookup value of 425 by the value returned (e.g., $30) giving me a result of $12,750. It's tough even describing what I want, but hopefully someone understands and can assist me ASAP. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value associated with a range
Hlookup won't work.
The OP seems to want the lowest value that is greater than the lookup value if there's not an exact match. Reverse the order of the table. 1450 1250 950 600 400 55 50 45 30 15 A5 = lookup value = 425 =IF(A5A1,A2,INDEX(A2:E2,MATCH(A5,A1:E1,-1))) Biff "Niek Otten" wrote in message ... Look in HELP for the HLOOKUP() function -- Kind regards, Niek Otten "Raymond Gallegos" wrote in message ... Hello. I am attempting to create a formula that searches through a range of values to identify where a number falls within the range and then, once identified, takes an associated value as a multiplier in the formula. Here is the range data: A B C D E 1 400 600 950 1250 1450 2 $15 $30 $45 $50 $55 Lookup value: 425 Based on these values, I need a formula to search a1 through e1 and return the value in B2 ($30) associated with 600 (because 425 is over 400, but not greater than 600) and allow me to then multiply the lookup value of 425 by the value returned (e.g., $30) giving me a result of $12,750. It's tough even describing what I want, but hopefully someone understands and can assist me ASAP. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value associated with a range
If you can put the values in descending order (row 1 has 1450, 1250...; row
2 has 55,50,...) then this will give required result =INDEX(A2:E2,1,MATCH(A8,A1:E1,-1)) best wishes Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Raymond Gallegos" wrote in message ... Hello. I am attempting to create a formula that searches through a range of values to identify where a number falls within the range and then, once identified, takes an associated value as a multiplier in the formula. Here is the range data: A B C D E 1 400 600 950 1250 1450 2 $15 $30 $45 $50 $55 Lookup value: 425 Based on these values, I need a formula to search a1 through e1 and return the value in B2 ($30) associated with 600 (because 425 is over 400, but not greater than 600) and allow me to then multiply the lookup value of 425 by the value returned (e.g., $30) giving me a result of $12,750. It's tough even describing what I want, but hopefully someone understands and can assist me ASAP. Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value associated with a range
Nick, thank you for the information. When I utilize HLOOKUP for some
reason it comes back with the value associated with 400 versus 600. In reading about the formula it denotes that if it can't find an exact match it will select the next highes value (which would be 600), which would be what I want, but it doesn't seem to be working. Any thoughts? "Niek Otten" wrote: Look in HELP for the HLOOKUP() function -- Kind regards, Niek Otten "Raymond Gallegos" wrote in message ... Hello. I am attempting to create a formula that searches through a range of values to identify where a number falls within the range and then, once identified, takes an associated value as a multiplier in the formula. Here is the range data: A B C D E 1 400 600 950 1250 1450 2 $15 $30 $45 $50 $55 Lookup value: 425 Based on these values, I need a formula to search a1 through e1 and return the value in B2 ($30) associated with 600 (because 425 is over 400, but not greater than 600) and allow me to then multiply the lookup value of 425 by the value returned (e.g., $30) giving me a result of $12,750. It's tough even describing what I want, but hopefully someone understands and can assist me ASAP. Thank you. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a value associated with a range
This will work without having to reverse the order, with 425 in A5
=INDEX(A2:E2,MATCH(SMALL(A1:E1,COUNTIF(A1:E1,"<"&A 5)+1),A1:E1,0)) hardcoded with 425 it would look like =INDEX(A2:E2,MATCH(SMALL(A1:E1,COUNTIF(A1:E1,"<425 ")+1),A1:E1,0)) -- Regards, Peo Sjoblom (No private emails please) "Raymond Gallegos" wrote in message ... Nick, thank you for the information. When I utilize HLOOKUP for some reason it comes back with the value associated with 400 versus 600. In reading about the formula it denotes that if it can't find an exact match it will select the next highes value (which would be 600), which would be what I want, but it doesn't seem to be working. Any thoughts? "Niek Otten" wrote: Look in HELP for the HLOOKUP() function -- Kind regards, Niek Otten "Raymond Gallegos" wrote in message ... Hello. I am attempting to create a formula that searches through a range of values to identify where a number falls within the range and then, once identified, takes an associated value as a multiplier in the formula. Here is the range data: A B C D E 1 400 600 950 1250 1450 2 $15 $30 $45 $50 $55 Lookup value: 425 Based on these values, I need a formula to search a1 through e1 and return the value in B2 ($30) associated with 600 (because 425 is over 400, but not greater than 600) and allow me to then multiply the lookup value of 425 by the value returned (e.g., $30) giving me a result of $12,750. It's tough even describing what I want, but hopefully someone understands and can assist me ASAP. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Finding the last value in a range of cells | Excel Discussion (Misc queries) | |||
Finding dates within a date range | Excel Worksheet Functions | |||
Finding Dates in a date range | Excel Discussion (Misc queries) |