A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

lookup tables in cells



 
 
Thread Tools Display Modes
  #1  
Old February 22nd, 2005, 10:13 PM
external usenet poster
 
Posts: n/a
Default 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?
  #2  
Old February 22nd, 2005, 10:29 PM
Trevor Shuttleworth
external usenet poster
 
Posts: n/a
Default

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?



  #3  
Old February 22nd, 2005, 10:52 PM
Brad Gover
external usenet poster
 
Posts: n/a
Default

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?



.

  #4  
Old February 22nd, 2005, 10:57 PM
CLR
external usenet poster
 
Posts: n/a
Default

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?



  #5  
Old February 22nd, 2005, 11:26 PM
Trevor Shuttleworth
external usenet poster
 
Posts: n/a
Default

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?



.



  #6  
Old February 28th, 2005, 10:47 AM
Aladin Akyurek
external usenet poster
 
Posts: n/a
Default

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?



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using LookUp tables in sql query,need help Julia Running & Setting Up Queries 7 August 31st, 2004 07:32 PM
LOOKUP functions from cells containing formula . . . adamhudson Worksheet Functions 1 July 22nd, 2004 08:20 PM
Sorting Lookup tables Keith Davis Database Design 2 May 24th, 2004 11:28 PM


All times are GMT +1. The time now is 09:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.