Hi,

I require a vlookup but can't get this to work for my example below.
In sheet 1 cell C5 I would like to enter a figure which will then do
SUM(80/100*c5) in C7
Based on the % given i would then like to get data from sheet 2 col H and
place in C18 on sheet 1.

Sheet 2 is arranged as follows: Col C is percentage going up in 5, and Col D
is the figure entered in sheet 1 C5 that will determine the % line to read
for Col H. The deviation is 5 percentage between each line.

c d e f g h
5 4 0.0
10 8 0.0
15 12 1.3
20 16 2.7
25 20 4.0
30 25 5.3
35 30 6.7

So if C5 sheet 1 is 16 it will read 2.7 in col H for c18 sheet 1. However if
18 it still needs to read 2.7 in col H until above 20 when it'll be 4.0.
I hope this is clear, but really can't link the two sheets to do this. Any
help would be much appreciated.

Haz

I'm confused as to why you showed lines of numbers in C and D column. From
your example, it appears that you are using column D to choose values, so I
used that in this formula.
=VLOOKUP($C$5,Sheet2!D$1:H$4,5)

Note that you mention you are using percentages, so the numbers in your
lookup table need to be percentages as well. Don't want to try and compare
0.6 (60%) with 60.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Haz" wrote:

Hi,

I require a vlookup but can't get this to work for my example below.
In sheet 1 cell C5 I would like to enter a figure which will then do
SUM(80/100*c5) in C7
Based on the % given i would then like to get data from sheet 2 col H and
place in C18 on sheet 1.

Sheet 2 is arranged as follows: Col C is percentage going up in 5, and Col D
is the figure entered in sheet 1 C5 that will determine the % line to read
for Col H. The deviation is 5 percentage between each line.

c d e f g h
5 4 0.0
10 8 0.0
15 12 1.3
20 16 2.7
25 20 4.0
30 25 5.3
35 30 6.7

So if C5 sheet 1 is 16 it will read 2.7 in col H for c18 sheet 1. However if
18 it still needs to read 2.7 in col H until above 20 when it'll be 4.0.
I hope this is clear, but really can't link the two sheets to do this. Any
help would be much appreciated.

Haz

hi thanks for your reply, The vlookup will only work if an exact match. What
I require is an if function that works with a deviation of 5 to distinguish
between rows from which to get data from Col H.

Example if sheet 1 cell C7 was 81.25% its needs to match this with sheet 2
c22 which is 80 and then get value in H22 and paste this in sheet 1 C6.

Sheet 2 col C is goes up in 5, i.e 5,10,15,20 and so on. the match is based
on where c7 fits between a difference of 5.

If c7 was 20 this would match exactly with sheet 2 C10 and then give value
in H10. but this is not always the case. So I guess an if function that works
within a 5 mark principle would then match my col c in sheet 2 and give
value corresponding in Col H.

hope this is clearer.
thanks again
"Luke M" wrote:

I'm confused as to why you showed lines of numbers in C and D column. From
your example, it appears that you are using column D to choose values, so I
used that in this formula.
=VLOOKUP($C$5,Sheet2!D$1:H$4,5)

Note that you mention you are using percentages, so the numbers in your
lookup table need to be percentages as well. Don't want to try and compare
0.6 (60%) with 60.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Haz" wrote:

Hi,

I require a vlookup but can't get this to work for my example below.
In sheet 1 cell C5 I would like to enter a figure which will then do
SUM(80/100*c5) in C7
Based on the % given i would then like to get data from sheet 2 col H and
place in C18 on sheet 1.

Sheet 2 is arranged as follows: Col C is percentage going up in 5, and Col D
is the figure entered in sheet 1 C5 that will determine the % line to read
for Col H. The deviation is 5 percentage between each line.

c d e f g h
5 4 0.0
10 8 0.0
15 12 1.3
20 16 2.7
25 20 4.0
30 25 5.3
35 30 6.7

So if C5 sheet 1 is 16 it will read 2.7 in col H for c18 sheet 1. However if
18 it still needs to read 2.7 in col H until above 20 when it'll be 4.0.
I hope this is clear, but really can't link the two sheets to do this. Any
help would be much appreciated.

Haz

Try something like this:

=VLOOKUP(C7,Sheet2!C$1H$20,6)

in Sheet1. This assumes that both C columns contain percentages.

Hope this helps.

Pete

On Dec 16, 11:09*am, Haz wrote:
hi thanks for your reply, The vlookup will only work if an exact match. What
I require is an if function that works with a deviation of 5 to distinguish
between rows from which to get data from Col H.

Example if sheet 1 cell C7 was 81.25% its needs to match this with sheet 2
c22 which is 80 and then get *value in H22 and paste this in sheet 1 C6..

Sheet 2 col C is goes up in 5, i.e 5,10,15,20 and so on. the match is based
on where c7 fits between a difference of 5.

If c7 was 20 this would match exactly with sheet 2 C10 and *then give value
in H10. but this is not always the case. So I guess an if function that works
*within a 5 mark principle would then match my col c in sheet 2 and give
value corresponding in Col H.

hope this is clearer.
thanks again



"Luke M" wrote:
I'm confused as to why you showed lines of numbers in C and D column. From
your example, it appears that you are using column D to choose values, so I
used that in this formula.
=VLOOKUP($C$5,Sheet2!D$1:H$4,5)

Note that you mention you are using percentages, so the numbers in your
lookup table need to be percentages as well. Don't want to try and compare
0.6 (60%) with 60.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Haz" wrote:

Hi,

I require a vlookup *but can't get this to work for my example below.
In sheet 1 cell C5 I would like to enter a figure which will then do *
SUM(80/100*c5) in C7
Based on the % given i would then like to get data from sheet 2 col H and
place in C18 on sheet 1.

Sheet 2 is arranged as follows: Col C is percentage going up in 5, and Col D
is the figure entered in sheet 1 C5 that will determine *the % line *to read
for Col H. The deviation is 5 percentage between each line.

c * * * *d * * * * e * * * * * f * * * *g * * * * h * * *
5 * * * 4 * * * * * * * * * * * * * * * * * * * * *0.0
10 * * 8 * * * * * * * * * * * * * * * * * * * * *0.0
15 * *12 * * * * * * * * * * * * * * * * * * * * 1.3
20 * *16 * * * * * * * * * * * * * * * * * * * * 2.7
25 * *20 * * * * * * * * * * * * * * * * * * * * 4.0
30 * *25 * * * * * * * * * * * * * * * * * * * * 5.3
35 * *30 * * * * * * * * * * * * * * * * * * * * 6.7

So if C5 sheet 1 is 16 it will read 2.7 in col H for c18 sheet 1. However if
18 it still needs to read 2.7 in col H until above 20 when it'll be 4..0.
I hope this is clear, but really can't link the two sheets to do this.. Any
help would be much appreciated.

Haz- Hide quoted text -

- Show quoted text -