#1   Report Post  
rajeev
 
Posts: n/a
Default 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   Report Post  
bj
 
Posts: n/a
Default

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   Report Post  
rajeev
 
Posts: n/a
Default

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   Report Post  
bj
 
Posts: n/a
Default

=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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Line Graph Data Recognition Nat Charts and Charting in Excel 2 April 30th 05 02:07 PM
compare data in two lists to find matching entries Chris(new user) Excel Discussion (Misc queries) 1 March 19th 05 10:49 PM
compare data in two lists to find matching entries Chris(new user) Excel Discussion (Misc queries) 0 March 19th 05 09:49 PM
Matching data in one column to another excel idiot Excel Worksheet Functions 1 January 14th 05 03:15 PM
Running Data Table using an input that triggers DDE linked data [email protected] Excel Discussion (Misc queries) 1 December 16th 04 12:56 PM


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

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

About Us

"It's about Microsoft Excel"