Compare 2 lists and print matches and associated values, plus extra complications

I have two lists, the first is a "main" list and the second is a "key" list.

The main list looks something like this, where each line is a separate cell, all contained on one column:

apples 23
apples 6
bananas 14
cake 5
ice cream vanilla
watrmelon store bought
apples 3
chicken 5

As you can see, each cell will contain 2 items - a food item and then an associated number or description. Also note that there might be not be the same amount of spaces after the first item, and that some of the first items include spaces (such as ice cream). Also note that some things may not be spelled 100% correctly (such as watermelon), so I need it to recognize pretty-close spellings.

The key list will look something like this, again each item in it's own cell:

apples
ice cream
chicken

I want Excel to look at the main list, and if it finds an item that's also on the key list, it will print that item in one cell, and then print it's corresponding value in the adjacent cell. So, for example, it would look at the main list, see "apples", see that apples appears on the key list, and then print apples in a new cell, and then in the adjacent cell print 23. Essentially I need Excel to look at a long list and "pull out" elements that correspond to a master list (key list).

At the same time, I also need to keep the same items together. So... in my example above, if Excel was matching the main list to the key list, it would print out into new cells apples, 23; apples, 6; ice cream, vanilla; apples, 3; chicken, 5. However, I want it to see that there are 2 apples grouped together, and have it move that third apples up next to the other 2 apples (essentially so that no items is on it's on).

So that is where I am at right now. I know most of the basic functions, but did not know where to start. I am very willing to do this in VBA if that is easier/more possible - I know a little, but need some help getting started. Thank you in advance to anyone who can help!

Thanks for the welcome!

I have not made any starts yet. I was thinking Vlookup but... would that still work if the data is in the same cell as the thing I'm trying to match? For instance if I want it to match apple with apple, but I want it to print 23 which is also in the same cell as apple.

So I went ahead and came up with a start, although I'm not sure it's the most efficient way to do it. If there is a more streamlined way, I would like to hear it.

What I did is I listed my main list in column A. So A2 is apples 23, A3 is apples 6, A4 is bananas 14, and so. Then in row 1, I have my key list. So B1 is apples, C1 is banana, D1 is Cake, and so on, so that they are column headings. In each column, I used the FIND function to find that column heading in the associated A cell. So for instance, the formula in B2 finds the column heading (apples) in A2, and if it finds it, it displays a 1. In this case it would find it and put a zero. In B3, it would find apple in A3 and also put a 1. However in B4, it would not find apple and therefore put a 0. Likewise, in the C column, the formula in C2 looks for the column heading (banana) in A2, and in C3 it would look for it in A3... and so on.

When all is said and done, I have a giant table of 0's and 1's. If there is a 1 in a cell, that means that the column heading is found in the first cell in that row (which is the main list). That way I can see what items in the main list match the key list. It works when there are only a few items in the key list, like my examples here... but ultimately my key list will contain about 100 items, and will be hard to manage this way.

I'm not sure where to go from here. This method seems very clunky and sloppy and just hard to manage. I am open to any other routes that might be more practical.

Thanks for the help. I attached the file, I hope it works ok. The workspace in question is on Sheet2, you can ignore Sheet1.

As you'll see, my "key" list is all the way to the side in column S. If something in the main list in column A is on the key list, I want it to print out JUST the item name. So I can up with this whole grid idea. Column I shows how I ultimately want everything - one simple list. I also don't want any blank cells in my final list, which is why I have the filter. But I want this to be automated as much as possible, and ultimately want to come up with a way to display the final list without any blank cells without messing with a filter. And on top of all that... I ultimately ultimately want the final list to stay in order, but also be group together. So, for instance, the first "apple" wouldn't move, however the last "apple" would move and would rest right under the first, so it would say apple apple banana. Likewise if there were any bananas later on down, they would all move up underneath the first banana.

It took me a while to figure out this much. I think I could keep going, but I'm beginning to wonder if it's the best method, because I plan to have about 100+ items in the key list, which means 100 different variations of the same formula. Not only that, but the table will become pretty big (I'll also have 100+ items in the main list), although maybe that doesn't matter so much.

Yes! That works great. Now is there a function that lets you copy one range into another, and then I can remove the blank cells?