LOOKUP problem

  • Thread starter Thread starter NOTTNICK
  • Start date Start date
N

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
 
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

NOTTNICK said:
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
Click to expand...

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

The message was checked by ESET Smart Security.

http://www.eset.com
 
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 -
Click to expand...

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