I have a master list of numbers in column A. I have 3 mini lists in columns C, D, and E. I need cell B1 to tell me which list A1 is in, and B2 to tell me which list A2, etc. In other words, I need to write a function that says “B cell, if A cell is found in column C, return the value C. If it’s in D, return the value D. If it’s in E, return the value E.”
It needs to be a function so that it’s dynamic. Yesterday, I did a VLOOKUP but I had to do one list at a time and then CONCATENATE them into one list. If I add or subtract from lists C, D, or E, I have to start over. I want the function to do it itself.
The value can be in one or all of the lists B C and D? So you may end up with a B cell with a value of “CDE”?
Is there an option to use additional columns. like a vlookup column for each of CDE columns, and a 4th column that concatenates the results of the first 3?
This does your vlookup on A1 in column C. If there is no error (VLOOKUP doesn’t return #NA), then your answer is “colC”.
If VLOOKUP returns an error, then it repeats the process for the next column. If it doesn’t find a column after going through all three, it returns “NoMatch”.
Is that what you’re looking for?
ETA: One thing I would note, this setup will only find the first occurence of your value, if it’s in more than one column.
For clarification, let’s just say it’s a baseball team roster. Each person is either a pitcher, fielder, or manager. I have 15,000 men on this team. 1,000 are managers, and they’re in column C. 7,000 are column D fielders and 7000 are column E pitchers. Each jersey number is used once an only once. Each player is in only one position on the team. So I might have something like:
A b c d
b i A e
c h j
d f g
e
f
g
h
i
j
As you can also see, they’re not sorted in the C, D, and E columns and they have to stay that way.
Your problem is that this database has been designed in a pretty daft way. Fortunately, there is a simple solution.
Reproduce serially in a single list the entries in columns C, D, and E. That is, in a new sheet, copy the n[sub]C[/sub] entries of column C to cells A1 through An[sub]C[/sub], then copy the n[sub]D[/sub] entries of column D to cells An[sub]C[/sub]+1 through An[sub]C[/sub]+n[sub]D[/sub], and finally the copy the n[sub]E[/sub] entries of column E to cells An[sub]C[/sub]+n[sub]D[/sub]+1 through An[sub]C[/sub]+n[sub]D[/sub]+n[sub]E[/sub]. As you copy each column into this new sheet, in column B, code for the source column (i.e., enter “C”, “D”, or “E”, according to the column the data were originally in).
Now you can run a VLOOKUP and get the pertinent column information.
If you are paranoid about preserving the order of the original columns, you can code their original position in the new spreadsheet as well. After you copy the list and code the original column letter, in column C, enter a formula that will label each successive cell one number greater. Then, as you finish copying over each individual column, copy the entire selection and use one of the most important tools in Excel: the paste values function. Now the column position data is hard-coded, rather than the result of a formula.
OK, I follow your example. Have you tried my solution? It should work.
If you’re not sure what your ranges are in the field-position columns, or if those lists may change, then you’d probably want to change the range you use for your table array in VLOOKUP to include the entire column (e.g., $C$1:$C$65536). It will take a bit longer to run that way, but should do what you need.
I tested your method with match and isnumber and got similar results. I don’t think there’s any advantage/disadvantage to either formula. This formula copied down the B1 column provides the same results. This too only picks up the first occurrence traveling left to right lists.
BubbaDog, I concur. It looks like your formula works as well as mine.
Yeah, the nested IFs. I’m not that fond of them, but somehow I seem to end up using 'em a lot, with lots of other formulas inside. I usually start from the inside and work out to the ifs.
Then I only have to fix the parens one or two places.
(Actually, given my druthers, I’ll usually dump it over into Access, where it’s a lot easier to do most complicated stuff.)
RedTail wins. The formula works for what I needed. Unfortunately, I couldn’t get it to just return a label. Instead I had to have it return the column header. Whatever. BubbaDog, yours looks cleaner and fits on one line, so you win honorable mention.
Thanks for introducing me to ISERROR and ISNUMBER too. Logged for future use.
As for the design being daft…well, that’s the gov’t for you. The master list came from a list of 15000 files in one folder that we had to do some filename surgery on. The other lists come from a completely different system. Trust me, there were some major obstacles to overcome. I had to reach back to the DOS prompt for one step of the process. The reason I can’t just combine the lists like suggested is because they’re incomplete as of yet. We’re done with Column C, moving through Column D, and E is blank right now. That’s why I needed a dynamic solution. If there is a number that comes up that’s not on the master list, or vice versa, I need a way to tell. This will let me do that easily.
Thanks. Now you can all say you’ve contributed to national security. If you’re wise, at the next cocktail party, you can dress it up with something like “The government called me and said they needed my expertise on a computer system. Only I could help.”
Is it? It sounds like you designed it, or at least you are perpetuating poor design choices. Until you apply the principles of mathematical database design, you’re just going to be jury-rigging increasingly complex solutions until it becomes unmanageable.* An ounce of prevention is worth a pound of cure, my friend, especially when it comes to complex, ten-thousand-entry databases.
This is the difference between an IT professional and a mere Excel technician.
Well if you’ve got a better idea, throw it out there. Your previous idea was good but the problem is we can’t just use the CDE lists. The goal of this is to take the master list of serial (not literally) numbers find out what group each one belongs to (C, D, or E) and most importantly, find out if any of them aren’t in the reference lists at all. Remember that the lists are changing daily.
No, they’re basically copy/paste but it’s a slow process to get all the numbers. The biggest obstacle is that they start on a different system than they finish on. It takes days to get them crossed over.
