Matching Data with multiple columns

Hi All,

I am trying to sanity check data between two sets.

By way of example, the first set contains three columns - Name, Food, Band (columns A,B&C). The second set contains identical headings (columns D,E & F)

I need Excel to match the contents of column D with column A and the test whether column E matches column B and column F matches column C. Where this is true, I then need excel to display "TRUE", and "FALSE" where this is not the case (example attached with my formula).

I have tried to do this with a combination of IF, VLOOKUP and AND - but its not working. Can anyone suggest what I am doing wrong, or a better way to do this?#

Please see attached example.

Thanks,


Jim,

Are you trying to find exact matches of all three in the row (A=D, B=E, and C+F)? If so, you could use:

Please Login or Register  to view this content.

Hi,

Thanks for the speedy response!

I've tried this formula, but sadly it isn't working either.

I think the problem is that the VLOOKUP function is stopping at the first instance it finds.

For instance the first item is "Mike, Cheese, Pink". But this doesn't appear until then sixth row. Therefore excel returns FALSE as it has found (& stopped at) "Mike, Sandwich, Red" in the third row and just stopped looking.

Can you suggest anything else I could try?

Many thanks,



Jim

Well, it's cheating a bit, but how about the attached?

Hi Bob,

Again thanks for the reply, forgive me if i'm being dumb - but this formula just seems to be cocantenating the data?

Right, but now you have a single "value" to check against a comparable data set, so you can find values that exactly match. That's the cheat. So then the formula in Column H is very simple, as you see in the example, and gets all instances of matching all three columns.

Right, absolutely with you now. Did't realise you'd amended the formula in H as well!

I see how this is working, but to complicate matters somewhat further I still need the N/A value to be returned in H7 - rather than the FALSE value currently returned.

Do you think I'd be better going about this with INDEX and MATCH instead? Or is there a fix for this problem?

Thanks again,

Jim

There will be some occasions when names will not appear in one set of data - such as Bill. Otherwise where "Mike" appears with "cheese" and "blue" it should appear as an error as it is not in both data sets (ie false).

Sorry, I don't follow. I see why Bill would be NA since he's not even on the list in Column A. But Cheese and Blue are, only the combination of Mike, Cheese, and Blue isn't a match. I'm trying to understand the rules for what should be FALSE and what should be NA.