Matching help

[SPIG]

Hello All,
I need some help with some matching data and i only have first and last names to go off of which can be a pain cause there is multiple orders on some. I need to look up 3 different fields between to tabs in a workbook which checks for last name, first name , and the product as a 3 verification. And then show a value in a cell to show true or false...So on sheet1 I have last name in column C, first name in D and the product J, On the sheet i need to match all that information with is on sheet2, lastname in P first name in O and the product in D. Or maybe someone may have a better way to match this stuff up, Im all ears.

See attached please
all advice and help greatly appreciated

[sweep]

Hi,

Does this work for you?

=SUMPRODUCT((Walmart!$P$1:$P$1846=C2)*(Walmart!$O$1:$O$1846=D2)*(Walmart!$D$1:$D$1846=J2))>0

in P2 and copied down the list of data..

[SPIG]

This works Great thank you

[SPIG]

Hi,

Does this work for you?

=SUMPRODUCT((Walmart!$P$1:$P$1846=C2)*(Walmart!$O$1:$O$1846=D2)*(Walmart!$D$1:$D$1846=J2))>0

in P2 and copied down the list of data..

This works Great... Thanks

[SPIG]

Hi,

Does this work for you?

=SUMPRODUCT((Walmart!$P$1:$P$1846=C2)*(Walmart!$O$1:$O$1846=D2)*(Walmart!$D$1:$D$1846=J2))>0

in P2 and copied down the list of data..

This works great as well thank you

[Syrkrasi]

use =IFERROR(IF(MATCH(1,(D2=Walmart!O:O)*(C2=Walmart!P:P)*(J2=Walmart!D:D),0),"TRUE",),"FALSE")

This is an array formula, you must simultaneously press the SHIFT + CTRL +ENTER keys to activate it (simply pressing the ENTER key will result in a false/positive answer or an error.)
Drag the formula down to last entry

See attachment

[SPIG]

use =IFERROR(IF(MATCH(1,(D2=Walmart!O:O)*(C2=Walmart!P:P)*(J2=Walmart!D:D),0),"TRUE",),"FALSE")

This is an array formula, you must simultaneously press the SHIFT + CTRL +ENTER keys to activate it (simply pressing the ENTER key will result in a false/positive answer or an error.)
Drag the formula down to last entry

See attachment

This works great. Just have to remember to make it an array
Thanks

[dflak]

What you are trying to do here is similar to a composite key in a database. What makes a record unique in this "database" is the combination of first name, last name and product description. Except that it isn't unique, but more about that later.

When comparing items like this make a "composite key" by concatenating the pieces together. I normally like to put in a delimiter to make it easier to read and this sometimes also removes ambiguity.=[@[First Name]]&":"&[@[Last Name]]&":"&[@[Item Name]]

I put things in Excel Tables because they duplicate formulas down and they know how big they are so that formulas that reference them don't have to change when the size of the data changes. Also, you can use column header names in formulas which makes them easier to understand.

Then match the composites of one table against the composites in the other table. =ISNUMBER(MATCH([@Composite],Table_WM[Composite],0)) - this formula yields TRUE if a match is found and FALSE if a match is not found.

Now to the second problem: The Pivot Sheet indicates that you have duplicate data. The same customer has ordered the same item multiple times. For example Debbie Coatney is really into popcorn. The match column only indicates that at least one payment has been made. So Debbie could have been paid only once for the 8 orders. I added an additional column to indicate how many payments have been made.