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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |