Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 ) |
#2
|
|||
|
|||
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 ) . |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions | |||
Lookup and Sort Question | Excel Discussion (Misc queries) | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |