Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting a value within a range
What function can i use to determine a value from a range?
A B c 0 300 0.6 301 600 0.8 601 900 1.0 901 1200 1.2 So if i have a value of 500, it will compare between A & B, to provide a value C. in this case 0.8. Can someone advise me the best way to achieve this? I have already generated a VLOOKUP for columns B & C, but not sure how to get a value that is between A and B. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting a value within a range
Hi,
You may try this =vlookup(A6,A1:C4,3). where A6 holds 500 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Steve HKG" wrote in message ... What function can i use to determine a value from a range? A B c 0 300 0.6 301 600 0.8 601 900 1.0 901 1200 1.2 So if i have a value of 500, it will compare between A & B, to provide a value C. in this case 0.8. Can someone advise me the best way to achieve this? I have already generated a VLOOKUP for columns B & C, but not sure how to get a value that is between A and B. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting a value within a range
Hi Ashish,
Thanks for your reply. But 500 would be "between" 301 and 600, and is not an exact value on the table. So I would just get #N/A in the cell with the forula that you have suggested. How can it get it to compare the values between A1 & B4 range? Then the answer of that provides the value in C1 - C4? Thanks. "Ashish Mathur" wrote: Hi, You may try this =vlookup(A6,A1:C4,3). where A6 holds 500 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Steve HKG" wrote in message ... What function can i use to determine a value from a range? A B c 0 300 0.6 301 600 0.8 601 900 1.0 901 1200 1.2 So if i have a value of 500, it will compare between A & B, to provide a value C. in this case 0.8. Can someone advise me the best way to achieve this? I have already generated a VLOOKUP for columns B & C, but not sure how to get a value that is between A and B. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting a value within a range
As Ashish suggested Vlookup should lookup a value within a range. Format the
table cells to number format and see. If this post helps click Yes --------------- Jacob Skaria "Steve HKG" wrote: Hi Ashish, Thanks for your reply. But 500 would be "between" 301 and 600, and is not an exact value on the table. So I would just get #N/A in the cell with the forula that you have suggested. How can it get it to compare the values between A1 & B4 range? Then the answer of that provides the value in C1 - C4? Thanks. "Ashish Mathur" wrote: Hi, You may try this =vlookup(A6,A1:C4,3). where A6 holds 500 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Steve HKG" wrote in message ... What function can i use to determine a value from a range? A B c 0 300 0.6 301 600 0.8 601 900 1.0 901 1200 1.2 So if i have a value of 500, it will compare between A & B, to provide a value C. in this case 0.8. Can someone advise me the best way to achieve this? I have already generated a VLOOKUP for columns B & C, but not sure how to get a value that is between A and B. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting a value within a range
Thanks. Have managed to get it to work now!
"Jacob Skaria" wrote: As Ashish suggested Vlookup should lookup a value within a range. Format the table cells to number format and see. If this post helps click Yes --------------- Jacob Skaria "Steve HKG" wrote: Hi Ashish, Thanks for your reply. But 500 would be "between" 301 and 600, and is not an exact value on the table. So I would just get #N/A in the cell with the forula that you have suggested. How can it get it to compare the values between A1 & B4 range? Then the answer of that provides the value in C1 - C4? Thanks. "Ashish Mathur" wrote: Hi, You may try this =vlookup(A6,A1:C4,3). where A6 holds 500 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Steve HKG" wrote in message ... What function can i use to determine a value from a range? A B c 0 300 0.6 301 600 0.8 601 900 1.0 901 1200 1.2 So if i have a value of 500, it will compare between A & B, to provide a value C. in this case 0.8. Can someone advise me the best way to achieve this? I have already generated a VLOOKUP for columns B & C, but not sure how to get a value that is between A and B. Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting a value within a range
=VLOOKUP(500,A1:C4,3,TRUE)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |