Lookup Question
 

Hi,
I need to be able to match 3 columns of data against a data range.

my data consists of four columns:
Ref; Area; Release; Linked Work #

I need to be able to list out results that don't conform to the results in
another table in excel (that has the Linked Work #, Area, and Release field).

Therefore any entry from the first table that doesn't exist (in it's
entirety, e.g. the 3 columns) would show "False" or some other identifier.

This is to work out if data has been linked correctly (table 2 showing how
data should be linked).

Could anyone advise of a formula to do this - Obviously lookup functionality
would allow me to do on one cell reference - would this allow multiples?

TIA - Al )

Let's say your data to check is in columns B through D
(Area/Release/Linked Work #) in Sheet1, and the data
table is in the same format (col. B-D) in Sheet2. In E1
of Sheet1, put:

=SUMPRODUCT((B1=Sheet2!$B$1:$B$100)*(C1=Sheet2!
$C$1:$C$100)*(D1=Sheet2!$D$1:$D$100))0

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi,
I need to be able to match 3 columns of data against a
data range.

my data consists of four columns:
Ref; Area; Release; Linked Work #

I need to be able to list out results that don't conform
to the results in
another table in excel (that has the Linked Work #,
Area, and Release field).

Therefore any entry from the first table that doesn't
exist (in it's
entirety, e.g. the 3 columns) would show "False" or some
other identifier.

This is to work out if data has been linked correctly
(table 2 showing how
data should be linked).

Could anyone advise of a formula to do this - Obviously
lookup functionality
would allow me to do on one cell reference - would this
allow multiples?

TIA - Al )
.

One way:

=ISNUMBER(MATCH(1,((Sheet2!$B$1:$B$1000=B1)
*(Sheet2!$C$1:$C$1000=C1)*(Sheet2!$D$1:$D$1000=D1) ),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match your data. I went from row 1 to 1000. But don't use
the whole column.

Mackay 1979 wrote:

Hi,
I need to be able to match 3 columns of data against a data range.

my data consists of four columns:
Ref; Area; Release; Linked Work #

I need to be able to list out results that don't conform to the results in
another table in excel (that has the Linked Work #, Area, and Release field).

Therefore any entry from the first table that doesn't exist (in it's
entirety, e.g. the 3 columns) would show "False" or some other identifier.

This is to work out if data has been linked correctly (table 2 showing how
data should be linked).

Could anyone advise of a formula to do this - Obviously lookup functionality
would allow me to do on one cell reference - would this allow multiples?

TIA - Al )

--

Dave Peterson