Lookup Question

[Guest]

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 ([email protected])

 

[Jason Morin]

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

 

[Dave Peterson]

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.