Vlookup help

[Haz]

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

 

[Luke M]

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.

 

[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

 

[Pete_UK]

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