Forum Discussion
Moving large subsets of Excel data from tab to another based on criteria
Here's your sample, with the formula I had suggested in Column B. I edited it to conform to your new arrangment of the two data sets, but that was a minimal change. The formula in cell B2 reads as follows:
=IFNA(VLOOKUP(A2,C$2:D$11,2,0),"")
I copied it down the length of your data set and you can see the result. I didn't bother with the color formatting, because you can just sort based on the presence of the word "Live" in column B.
mathetes Attached is a very simple example. The first sheet/tab shows the lists I have placed side by side with the hopes of identifying exact matches. I am looking to compare column C to column A. Once the exact matches have been identified and highlighted (green in this example).............see sheet/tab 2, I will just sort column A by color and choose only the green highlighted companies (aka the EXACT matches). "Company 3" and "Company 3 Asia" would not be exact matches. Once I have them sorted, I will manually enter the word Live into the B column and now have all of my live accounts identified, and then just delete columns C and D because they will no longer needed. I'm sure there are more elegant ways of doing this, but I could not get the other equations to work. So this seemed like the simplest way. Although when I tried it, I was picking up matches that were not exact. Hope this is more clear now and I greatly appreciate your patience and assistance.
Here's your sample, with the formula I had suggested in Column B. I edited it to conform to your new arrangment of the two data sets, but that was a minimal change. The formula in cell B2 reads as follows:
=IFNA(VLOOKUP(A2,C$2:D$11,2,0),"")
I copied it down the length of your data set and you can see the result. I didn't bother with the color formatting, because you can just sort based on the presence of the word "Live" in column B.
mathetes Essentially I have a list of customers that have gone live. Your formula has beautifully inserted them. However, within my customer list I also have existing statuses such as 'new' or a 'target go live date' that the formula overwrites with a blank. It sounds like there is no easy fix, so I can just past the formula around the existing statuses. Thanks again.
You'd need to describe the situation a bit more...but that formula takes the place of whatever is there (that's just the nature of it).
So if you have some other status descriptions that you also want to recognize, maybe we need to make this more complex, like having a table with various descriptors of criteria for determining what status ("Live" being only one of the possibilities).....
I think I figured out the 2. The 2 is column number that has what you are wanting to pull into the cell.
Correct.
I read recently that VLOOKUP is the most used function in Excel after the obvious ones that everybody learns in their first Excel course. It, along with its cousin functions (INDEX, MATCH, and the like) is well worth becoming familiar with.
And, yes, the 0 means "exact match." That is often used, but there are times when an inexact match is more useful. For example, if you were giving letter grades to students, an A could be the result for any number above 90, a B for any number between 80 and 89, etc.