Excel: Identify which column has the desired value
Posted on 2013-01-29
I have a list of values in one sheet. On another sheet, I have multiple values in various different columns. Each column has unique values, so there is no chance that a value is on two different columns. What I want is some formula (macro not desired, only if unavoidable) that will identify which column it comes from. I know I can achieve this using chained IFs and VLookups, but those seem inefficient and only work for a set number of columns.
Using this, if I want to add new columns, I have to add another chained IF. Also, it would seem that this would become very slow, very soon. The first list has about 30k rows. Each column on the second sheet will have something between 1k and 7k.
So, what is the best solution for this? Notice that the names Col1/Col2 aren't mandatory, though some way for me to customize the output is desirable.