MATCHING OF DATA

[rajeev]

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

 

[Guest]

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]

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

 

[Guest]

=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)