Vlookup

[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

 

[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

 

[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!A3454,2). When I input 6
instead of B6 the formula works.

Any help would be much appreciated

 

[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

 

[Arvi Laanemets]

Hi

The formula in B6 returns "6", not 6. Probably in range Sheet1!A3454 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!A3454,2)

 

[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

 

[Beginner]

Take that all back - thank you so much it does work now!