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. |
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. |
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. |
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. |
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. |
Getting a value within a range
=VLOOKUP(500,A1:C4,3,TRUE)
|
All times are GMT +1. The time now is 01:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com