Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MATCHING OF DATA
Hi,
I am working on a data in which Column A,B&C of sheet1 contains the following: PART NO. DESCRIPTION AMOUNT In case of some of data there is no part No. only description. In sheet2 i uses VLOOKUP to get the AMOUNT with LOOKUP values related to part no. but i am facing problem in case of components that does not have Part Nos. How can i get the values in the same column for components with or without part no. Thanks in advance. Regards Rajeev |
#2
|
|||
|
|||
What do you use for identification for those parts with no part number?
is the description unique enough that it can be used? if so and you have a table with the description value combo try something like =if(iserror(Vlookup[based on part number with false for 4th entry]),vlookup [based on description with false for 4the entry],Vlookup[based on part number]) "rajeev" wrote: Hi, I am working on a data in which Column A,B&C of sheet1 contains the following: PART NO. DESCRIPTION AMOUNT In case of some of data there is no part No. only description. In sheet2 i uses VLOOKUP to get the AMOUNT with LOOKUP values related to part no. but i am facing problem in case of components that does not have Part Nos. How can i get the values in the same column for components with or without part no. Thanks in advance. Regards Rajeev |
#3
|
|||
|
|||
Good morning bj,
The columns are blank for components for which there is no part number. Yes the description is unique. I am not able to go through the formula suggested by you.Will you please explain how i can use the same. Regards Rajeev |
#4
|
|||
|
|||
=if(iserror(Vlookup[based on part number with false for 4th entry]),vlookup
[based on description with false for 4the entry],Vlookup[based on part number]) You said you had a Vlookup to find information based on the rart number I assume it is something like =vlookup(Sheet2!A1,Sheet1!A1:C100,3) if you add a fourth term "false" to make it be =vlookup(Sheet2!A1,Sheet1!A1:C100,3, false) If you have a different method of getting informaion other than by description. When You don't have a part number, just put in the appropriate formula in palce of what I call the by Description section it forces an exact match for the part number with an "#NA returned if there is no match The iserror function is a logical function with a true or false response using it in an if statement with the Vlookup by part number says says if there is no part number matching what is in Cell A1 on sheet two, look for the information based on the description something like =Vlookup(Sheet2!A1,Sheet1!B1:C100,2,false) "rajeev" wrote: Good morning bj, The columns are blank for components for which there is no part number. Yes the description is unique. I am not able to go through the formula suggested by you.Will you please explain how i can use the same. Regards Rajeev |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
compare data in two lists to find matching entries | Excel Discussion (Misc queries) | |||
compare data in two lists to find matching entries | Excel Discussion (Misc queries) | |||
Matching data in one column to another | Excel Worksheet Functions | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |