Vlookup

B

Beginner

Hi

Can anyone tell me how you can ref a cell in a vlookup formula for the
lookup_value? I keep getting a #N/A error if I put say B6 but when I input
the actual number that my ref cell B6 holds, say 6, it works.

Thanks
 
A

Arvi Laanemets

Hi

The formula in B6 returns the value of which type? Maybe you post here the
formula from cell B6, the value displayed in B6, and your VLOOKUP formula.


Arvi Laanemets
 
B

Beginner

Formula in B6 is
=LOOKUP(D5,{0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100},{"1","1.5","2","2.5","3","3.5","4","4.5","5","5.5","6","6.5","7","7.5","8","8.5","9","9.5","10","10.5","11"})
therefore this gives me a result of 6, as D5 returns figure of 50.

Then Vlookup formula is =VLOOKUP(B6,SHEET1!A34:D54,2). When I input 6
instead of B6 the formula works.

Any help would be much appreciated
 
P

peter

Hi,
see if this helps.
If I understand your data...
cell a44 should have a 6 and cell b44 should have a 50
If your vlookup is in cell b6 you need a 6 in d5 then...
=vlookup(d5,a34:b54,2,false) will return a 50 in cell b6

peter
 
A

Arvi Laanemets

Hi

The formula in B6 returns "6", not 6. Probably in range Sheet1!A34:D54 are
numbers, i.e. somewhere there is a number 6
6=6, but 6<>"6"

Possible actions:
1. In B6, use the formula

=LOOKUP(D5,{0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100},{
1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10,10.5,11})

2. Replace your VLOOKUP formula with
=VLOOKUP("" & B6,SHEET1!A34:D54,2)
 
B

Beginner

Thanks for your help, but unfortunately that doesn't work either.

This is really starting to do my head in now! Maybe it just can't be done,
& will have to be done manually
 
Top