Comparing two text columns and return data based on an exact match

Hi,

First, thank you in advance for your assistance. I've got two columns with over 3500 entries and would like to compare the text strings in Column 1 with Column 2 for an exact match. If there's an exact match, I'd like to return the values found in Columns 3 and 4 into new Columns 5 and 6. I believe I can do this using the match and index functions. With my actual data, I may also need to use wildcards since the data contains multiple sentences in the cell.
I'd appreciate it if someone can assist with this. Thank you!

Example below:

Column 1 Column 2 Column 3 Column 4
cat orange red sky
dog doggie blue cloud
tiger kangaroo green sun
elephant cow orange moon
mouse cat one star
apple lion two rain
pear tiger three thunder
orange dog four snow

The first entry in new columns 5 and 6 should return "one" and "star" respectively.

Originally Posted by abousetta

Hello and welcome to the forum,

In my example, I used vlookup to accomplish this in the first group. In the second group, I assumed that column 2 was a sentence and used wildcards. Let me know if this is what you are after.

abousetta

P.S. Do a google search for my name and Advanced VLookup and you find other great examples as I have a thread dedicated to this topic.

Thank you for your assistance. This is close to what I'm trying to accomplish. Looking at your example with the wildcard formulas, I need to have an exact match. If the formula is comparing "cat" and then it must be a perfect match to "cat", not "cat and dog". Also, the actual data that I'm working with contains a paragraph in each cell. Is there a limit to the size of the cell that Excel can handle when trying to accomplish this task?

Thank you again!

Sorry, I didn't do a good job of explaining what I was trying to accomplish. Let me start again. I took your feedback and created a sample spreadsheet. For the most part, it works. However, I'm not sure why Row 5 doesn't show a match. It should be identical, since it's a cut and paste. I thought that might be due to a character limitation and why I asked the question regarding character limitations.



The other, more confusing question is actually part 2 of what I'm trying to do. My data list is a combination of exact matches and some that another colleague slightly modified. For instance, if the original cell contained three sentences, he may have eliminated one of the sentences, leaving the other two in tact. After running through the first pass of exact matches, I was hoping to use the wildcard formula to help me identify the modified cells. In my previous post, I combined my two goals and that was probably the confusing piece.