Lookup Problem

[Annette]

I have my order form so that when a user enters the product number, the
product information will display in the appropriate spaces (i.e., the full
description of the product). Here's the formula I'm using:

=LOOKUP(L20,Products!$C$2:$C$85,Products!$B$2:$B$85)

However, the spreadsheet gives incorrect information when it sees text in on
the PRODUCTs sheet. An example:

colb colc
toys 2848395
horses 8392879
houses see price list
rugs 8237499

If I type in the 'rugs', it produces the 'see price list'. Can anyone tell
me how to correct this?

 

[RagDyer]

I believe you have a typo in your posted formula.
If you type rugs into L20, the formula will look in the *first* range
(lookup vector), which is *not* Column C, according to your datalist
example.

But, that's neither here nor there.

When using LOOKUP(), your lookup column *must* be sorted.

That's why it's usually better to use VLOOKUP() or the INDEX() & MATCH()
combination, depending on the position of the lookup column in the datalist.

If your datalist example is correct (lookup column on the left), you can try
this Vlookup formula:

=VLOOKUP(L20,$B$2:$C$85,2,0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have my order form so that when a user enters the product number, the
product information will display in the appropriate spaces (i.e., the full
description of the product). Here's the formula I'm using:

=LOOKUP(L20,Products!$C$2:$C$85,Products!$B$2:$B$85)

However, the spreadsheet gives incorrect information when it sees text in on
the PRODUCTs sheet. An example:

colb colc
toys 2848395
horses 8392879
houses see price list
rugs 8237499

If I type in the 'rugs', it produces the 'see price list'. Can anyone tell
me how to correct this?