Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with Look up.
I have the following: -
Col B Col C Col D Row 25 127.8 Row 26 Row 27 Row 28 110 £86.49 Row 29 111 €“ 114 £89.07 Row 30 115 €“ 118 £91.65 Row 31 119 €“ 122 £94.23 Row 32 123 €“ 126 £96.81 Row 33 127 €“ 130 £99.31 Row 34 130 102.00 I need to compare the value stored in D25 against B28:B34 and when a value is found to report out the corresponding value from C28:C34. Therefore if D25 were 127.8, I would expect to see a value of £99.31 to be populated in cell F32. Thank you in anticipation. |
#2
|
|||
|
|||
Seperate colum B into 2 columns... Lower Range and upper range..
then the values in each column will be numeric and you can use simple lookups. "Pank" wrote: I have the following: - Col B Col C Col D Row 25 127.8 Row 26 Row 27 Row 28 110 £86.49 Row 29 111 €“ 114 £89.07 Row 30 115 €“ 118 £91.65 Row 31 119 €“ 122 £94.23 Row 32 123 €“ 126 £96.81 Row 33 127 €“ 130 £99.31 Row 34 130 102.00 I need to compare the value stored in D25 against B28:B34 and when a value is found to report out the corresponding value from C28:C34. Therefore if D25 were 127.8, I would expect to see a value of £99.31 to be populated in cell F32. Thank you in anticipation. |
#3
|
|||
|
|||
The ranges in col B trouble me. If the value in D25 was 126.8, it'd fall
between the range in row 32 and the range in row 33. Aside from that, change B to the upper end of each range: 110 114 118 etc. then use a VLOOKUP() formula =VLOOKUP(D25,B28:C34,2,0) "Pank" wrote: I have the following: - Col B Col C Col D Row 25 127.8 Row 26 Row 27 Row 28 110 £86.49 Row 29 111 €“ 114 £89.07 Row 30 115 €“ 118 £91.65 Row 31 119 €“ 122 £94.23 Row 32 123 €“ 126 £96.81 Row 33 127 €“ 130 £99.31 Row 34 130 102.00 I need to compare the value stored in D25 against B28:B34 and when a value is found to report out the corresponding value from C28:C34. Therefore if D25 were 127.8, I would expect to see a value of £99.31 to be populated in cell F32. Thank you in anticipation. |
#4
|
|||
|
|||
Tom, Duke,
Firstly many thanks for your help. I have tried Duke, option and I have the following:- Col B now contains:- 109, 110, 114, 118, 122, 126, 130 and 131 starting in B28 ending B35 . D25 I have formated as a number with 0 decimal places (the value in there at the moment is 128, without the formating it would have been 127.60) . Col C contains the appropriate monetary value. I have inserted the formula =VLOOKUP(D25,B28:B35,2,0) in cell F33, but it returns #N/A Have you any suggestions Thanks. "Duke Carey" wrote: The ranges in col B trouble me. If the value in D25 was 126.8, it'd fall between the range in row 32 and the range in row 33. Aside from that, change B to the upper end of each range: 110 114 118 etc. then use a VLOOKUP() formula =VLOOKUP(D25,B28:C34,2,0) "Pank" wrote: I have the following: - Col B Col C Col D Row 25 127.8 Row 26 Row 27 Row 28 110 £86.49 Row 29 111 €“ 114 £89.07 Row 30 115 €“ 118 £91.65 Row 31 119 €“ 122 £94.23 Row 32 123 €“ 126 £96.81 Row 33 127 €“ 130 £99.31 Row 34 130 102.00 I need to compare the value stored in D25 against B28:B34 and when a value is found to report out the corresponding value from C28:C34. Therefore if D25 were 127.8, I would expect to see a value of £99.31 to be populated in cell F32. Thank you in anticipation. |
#5
|
|||
|
|||
Duke,
I have sorted it out, I forgot to put in the whole range and hence the #N/A. Once again many thanks for your help. "Duke Carey" wrote: The ranges in col B trouble me. If the value in D25 was 126.8, it'd fall between the range in row 32 and the range in row 33. Aside from that, change B to the upper end of each range: 110 114 118 etc. then use a VLOOKUP() formula =VLOOKUP(D25,B28:C34,2,0) "Pank" wrote: I have the following: - Col B Col C Col D Row 25 127.8 Row 26 Row 27 Row 28 110 £86.49 Row 29 111 €“ 114 £89.07 Row 30 115 €“ 118 £91.65 Row 31 119 €“ 122 £94.23 Row 32 123 €“ 126 £96.81 Row 33 127 €“ 130 £99.31 Row 34 130 102.00 I need to compare the value stored in D25 against B28:B34 and when a value is found to report out the corresponding value from C28:C34. Therefore if D25 were 127.8, I would expect to see a value of £99.31 to be populated in cell F32. Thank you in anticipation. |
#6
|
|||
|
|||
Hi Pank, Use: =IF(ISERR(VLOOKUP(D25,B28:B35,2,0)),"",VLOOKUP(D25 ,B28:B35,2,0)) This will show a blank instead of #N/A. You get the #N/A when the lookup value does not exist in the table. Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=374529 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|