lookup tables in cells
 

Hi all. I was wondering how to error trap inported data
that uses one column as a lookup. i.e., that column has
imported part numbers and the column next to it looks up a
discription for that part number in another worksheet and
displays it next to the imported part number. The problem
is if the part number and discription is not in the lookup
table, the cell unfortunately reverts to the previous cell
in the lookup table and displays it. It does not show an
error or something to indicate no match for that cell part
number in the discription cell. Any solutions?

what formula are you using for the lookup ?

Regards

Trevor


wrote in message
...
Hi all. I was wondering how to error trap inported data
that uses one column as a lookup. i.e., that column has
imported part numbers and the column next to it looks up a
discription for that part number in another worksheet and
displays it next to the imported part number. The problem
is if the part number and discription is not in the lookup
table, the cell unfortunately reverts to the previous cell
in the lookup table and displays it. It does not show an
error or something to indicate no match for that cell part
number in the discription cell. Any solutions?

The formula is
=LOOKUP(A5,Table!$A$1:$A$500,Table!$B$1:$B$500)

Cell A5 contains a number like 100236.
Worksheet "Table" cell range contains $A$1:$A$500 part
numbers in increasing order. Cell range $B$1:$B$500
contains the discriptions. Everything works fine until a
part number is imported that has no part number to
reference in the table. Appriciate any advice.

-----Original Message-----
what formula are you using for the lookup ?

Regards

Trevor


wrote in message
...
Hi all. I was wondering how to error trap inported data
that uses one column as a lookup. i.e., that column has
imported part numbers and the column next to it looks
up a
discription for that part number in another worksheet
and
displays it next to the imported part number. The
problem
is if the part number and discription is not in the
lookup
table, the cell unfortunately reverts to the previous
cell
in the lookup table and displays it. It does not show
an
error or something to indicate no match for that cell
part
number in the discription cell. Any solutions?


.

Try adding the last condition "FALSE" to your formula.........
for example: =VLOOKUP(A1,YourTable,2,FALSE)

it should then return the real thing or an error........

Vaya con Dios,
Chuck, CABGx3


wrote in message
...
Hi all. I was wondering how to error trap inported data
that uses one column as a lookup. i.e., that column has
imported part numbers and the column next to it looks up a
discription for that part number in another worksheet and
displays it next to the imported part number. The problem
is if the part number and discription is not in the lookup
table, the cell unfortunately reverts to the previous cell
in the lookup table and displays it. It does not show an
error or something to indicate no match for that cell part
number in the discription cell. Any solutions?

Brad

the lookup should be:

=VLOOKUP(A5,Table!$A$1:$B$500,2,FALSE)

If there is no other data in the columns, you can shorten this to:

=VLOOKUP(A5,Table!$A:$B,2,FALSE)

Regards

Trevor


"Brad Gover" wrote in message
...
The formula is
=LOOKUP(A5,Table!$A$1:$A$500,Table!$B$1:$B$500)

Cell A5 contains a number like 100236.
Worksheet "Table" cell range contains $A$1:$A$500 part
numbers in increasing order. Cell range $B$1:$B$500
contains the discriptions. Everything works fine until a
part number is imported that has no part number to
reference in the table. Appriciate any advice.

-----Original Message-----
what formula are you using for the lookup ?

Regards

Trevor


wrote in message
...
Hi all. I was wondering how to error trap inported data
that uses one column as a lookup. i.e., that column has
imported part numbers and the column next to it looks
up a
discription for that part number in another worksheet
and
displays it next to the imported part number. The
problem
is if the part number and discription is not in the
lookup
table, the cell unfortunately reverts to the previous
cell
in the lookup table and displays it. It does not show
an
error or something to indicate no match for that cell
part
number in the discription cell. Any solutions?


.

Since the lookup table is sorted in ascending order on its first column,
the following would allow you exploit that fact...

=IF(LOOKUP(A5,Table!$A$1:$A$500)=A5,LOOKUP(A5,Tabl e!$A$1:$B$500),"")

without unexpected return values.

Brad Gover wrote:
The formula is
=LOOKUP(A5,Table!$A$1:$A$500,Table!$B$1:$B$500)

Cell A5 contains a number like 100236.
Worksheet "Table" cell range contains $A$1:$A$500 part
numbers in increasing order. Cell range $B$1:$B$500
contains the discriptions. Everything works fine until a
part number is imported that has no part number to
reference in the table. Appriciate any advice.


-----Original Message-----
what formula are you using for the lookup ?

Regards

Trevor


wrote in message
...

Hi all. I was wondering how to error trap inported data
that uses one column as a lookup. i.e., that column has
imported part numbers and the column next to it looks

up a

discription for that part number in another worksheet

and

displays it next to the imported part number. The

problem

is if the part number and discription is not in the

lookup

table, the cell unfortunately reverts to the previous

cell

in the lookup table and displays it. It does not show

an

error or something to indicate no match for that cell

part

number in the discription cell. Any solutions?


.