LOOKUP problem

[NOTTNICK]

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

 

[Roger Govier]

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

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.

http://www.eset.com

 

[NOTTNICK]

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











__________ Information from ESET Smart Security, version of virus signature database 4742 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com- Hide quoted text -

- Show quoted text -

Hey Roger
That's really neat.
It's done the job percfectly
Cheers
Nick