LOOKUP problem
NOTTNICK
his lecturers at college.
The LOOKUP function seems perfect for one task.
On my laptop the function
=LOOKUP(B31,{"Plaster","Curtains","Carpet","Glass","Wood
Panels","Acoustic Tiling","Plasterboard","Brick","Acoustic
Foam","Glass Window","Wood Door"},
{0.01,0.07,0.02,0.35,0.3,0.09,0.29,0.03,0.24,0.35,0.3})
Seems to work fine. It looks for the word in cell B31 and gives a
value in its place.
However, on his computer using same excel version / spreadsheet, it
does not give the correct value, but seems to pick a random value from
the list. Or comes up with an error message.
Any ideas?
Nick
Roger Govier
It cannot be working correctly on your computer either.
For Lookup to work, that data has to be in Ascending Order
Rather than putting the values into the formula, I prefer to put the data on
another sheet in columns A and B
=LOOKUP(B31,Sheet2!$A$1:$B$11)
having sorted column A Ascending.
If you use VLOOKUP instead of LOOKUP, then you don't have to have the data
sorted.
=VLOOKUP(B31,Sheet2!$A$1:$B$11,2,0)
The Vlookup of the first parameter (B31)always takes place in the first
column of the lookup Range.
The second parameter is the Lookup range (Sheet1!A1:B11
The third parameter, (,2 ) tells it to take the value from the second column
in the range
The optional 4th parameter of 0 (or FALSE) tells it to find an exact match.
--
Regards
Roger Govier
"NOTTNICK" <nott...@googlemail.com> wrote in message
news:c8f82a10-ac63-4bbc...@35g2000yqa.googlegroups.com...
> __________ Information from ESET Smart Security, version of virus
> signature database 4742 (20100104) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
__________ Information from ESET Smart Security, version of virus signature database 4742 (20100104) __________
The message was checked by ESET Smart Security.
NOTTNICK
wrote:
> Hi Nick
>
> It cannot be working correctly on your computer either.
> For Lookup to work, that data has to be in Ascending Order
>
> Rather than putting the values into the formula, I prefer to put the data on
> another sheet in columns A and B
> =LOOKUP(B31,Sheet2!$A$1:$B$11)
> having sorted column A Ascending.
>
> If you use VLOOKUP instead of LOOKUP, then you don't have to have the data
> sorted.
> =VLOOKUP(B31,Sheet2!$A$1:$B$11,2,0)
> The Vlookup of the first parameter (B31)always takes place in the first
> column of the lookup Range.
> The second parameter is the Lookup range (Sheet1!A1:B11
> The third parameter, (,2 ) tells it to take the value from the second column
> in the range
> The optional 4th parameter of 0 (or FALSE) tells it to find an exact match.
>
> --
> Regards
> Roger Govier
>
>
> news:c8f82a10-ac63-4bbc...@35g2000yqa.googlegroups.com...
>
>
>
>
>
> > I am trying to help my son produce a spreadsheet which will impress
> > his lecturers at college.
> > The LOOKUP function seems perfect for one task.
> > On my laptop the function
> > =LOOKUP(B31,{"Plaster","Curtains","Carpet","Glass","Wood
> > Panels","Acoustic Tiling","Plasterboard","Brick","Acoustic
> > Foam","Glass Window","Wood Door"},
> > {0.01,0.07,0.02,0.35,0.3,0.09,0.29,0.03,0.24,0.35,0.3})
> > Seems to work fine. It looks for the word in cell B31 and gives a
> > value in its place.
>
> > However, on his computer using same excel version / spreadsheet, it
> > does not give the correct value, but seems to pick a random value from
> > the list. Or comes up with an error message.
> > Any ideas?
>
> > Nick
>
> > __________ Information from ESET Smart Security, version of virus
> > signature database 4742 (20100104) __________
>
> > The message was checked by ESET Smart Security.
>
> >http://www.eset.com
>
> __________ Information from ESET Smart Security, version of virus signature database 4742 (20100104) __________
>
> The message was checked by ESET Smart Security.
>
>
> - Show quoted text -
Hey Roger
That's really neat.
It's done the job percfectly
Cheers
Nick