Help with Look up.

[Pank]

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.

[TomHinkle]

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.
>
>
>

[Duke Carey]

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.
>
>
>

[Pank]

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.
> >
> >
> >

[mangesh_yadav]

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

[Pank]

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.
> >
> >
> >