miss and match on excel

I'm not even sure if what i want to happen is possible but here it is : So i have a list on column A and another on column B. Say, column A has apples, bananas, eggs... Then column B has apple pies, banana pies, egg pies...on column C, I want excel to tell me that apples and apple pies match and bananas and banana pies match, and so on. Then tell me if they don't match at all. I want it so that even if i want 2 values from column A to match with a value in column B. For example aside from apples there could also be pears in column A but i would still want pears to return a match value with apple pies. I hope this is not too confusing ... Thanks in advance!

Answers


Ok, I am going to give you multiple answers because I am not sure what the question is. If Gary's Student's comment is the right answer and you just want to match the contents then all you need to do is put this in C2 and copy it to every row:

=IF(ISERROR(FIND(A2,B2)),"no match","match")

But if your comment about 1-2=bad, 3-8=good and 9-10=very good is what you really want then all you need to do is put this in C2 and copy it to every row:

=IF(B2=CHOOSE(A2,"bad","bad","good","good","good","good","good","good","very good","very good"),"match","no match")

What that does is change the number in column A to be the equivalent string and then match that string against column B.

But if you are looking for a solution that works for both things then you need a dictionary. If you use ALL the piossible values from B as the keys and each matching value from A as the values then you can call MATCH twice and see if the value belongs to the key.

So for example the dictionary for the pie example might look like this (notice that pies can have more than one ingredient):

    E          F          G          H          I          J          K
 1  Key        Value 1    Value 2    Value 3    Value 4    Value 5    Value 6
 2  lemon pie  lemon      egg
 3  apple pie  apple      pear
 4  pear pie   pear 
 5  egg pie    egg  

And the dictionary for the bad and good example would look like:

    E          F          G          H          I          J          K
 1  Key        Value 1    Value 2    Value 3    Value 4    Value 5    Value 6
 2  bad        1          2
 3  good       3          4          5          6          7          8
 4  very good  9          10

Then you can stick this in C2 and copy it to every row:

=IF(ISNA(MATCH(A2,OFFSET(F$1:O$1,MATCH(B2,E$2:E$20,0),0),0)),"no match","match")

What this does is look up the value in column B in the dictionary and find what row it is in. Then it offsets F$1:O$1 by that many rows so that the range for the outside match is the correct list of incgredients. Then it matches the value in column A by the list of ingredients that the offseted inside match produced.

Maybe some screenshots will help:


Need Your Help

Show xml file info into Listview C#

c# xml winforms linq

I'm working on a C# Desktop App and in a module I display the info of a xml file into a listview, I coded my solution with Linq to XML like this

Sqoop Incremental import and update

oracle hadoop hive sqoop

I am trying to import data from sql into a hive database. The goal is to update the changes in the oracle database to hive using sqoop import. The sqoop command is as follows: