Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I need to run a vlookup on the data in a column, which isn't a problem once I can sort the data out, but I can't work out how to get the data into a format where I only have to run one vlookup on it. The issue is that some cells contain multiple references, each of which needs to be checked, and each cell may contain between 1 and 15 references. I can do "Text to columns" to split out the individual references, but is there a formula to then combine all the columns into one? Alternatively, is it possible to remove duplicates over multiple columns? That would probably reduce the number of entries to be checked by about 50% or more, and I'm feeling lazy...
Don't really understand what you're trying to achieve without seeing it, but to combine the values of multiple columns into one string just use =concatenate
It's the reverse of that - I need to split the cells with multiple values, then put all of the values into a single column...
You can combine individual entries using "&" so in a cell have '="A" & "B"' or '=C206&C207'
If you want to do a lookup against a specific portion of a cell you can use LEFT, RIGHT or MID.
e.g. if you want to do a lookup against only the numbers in this string ABC123DEFG and it was in cell A1 then you could do
=VLOOKUP(MID(A1,4,3),"range where looking up","column number",0)
This relies on the lookup portion being in the same place each time. There is a way to do it with variable lenght references but its beyond me.
Jfletch is on the mark there. And for the person trying to combine cells into one.... to combine cells so that a1 and b1 are combined in c1
=a1 & b1
That will piece them together with no gaps. Should you wish to add a space
=a1 & " " & c1
Then run your query on the newly formed cell.
stealthcat - I do this a lot of the time to create "unique identifiers" for just that purpose.
as mccraque says use & to combine cell values into unique codes.
the CONCATENATE is a more powerful function to do the same exercise.
More useful than LEFT, RIGHT and MID on their own may be to use FIND and LEN as well. That allows you to split the multiple fields based on the separator. It will take a little while to write the formula but then it should be easy - if I've understood the problem correctly!
IF I've interpreted your description correctly, how about an INDEX MATCH using a wildcard concatenation on your match value? Values must be formatted as text.
[img] 
[/img]
Lookup array is A1:B3, with 2 matchable values in A1
Lookup values are in A9:A12 with results in B9:B12
Militant biker - that looks like it's very nearly the solution, but is there a way to set it up so that the lookup array has the multiple values? The problem I've got is that I know some of the multiple values are duds, but short of checking them all manually, I can't identify all of them...
Thanks!
is there a way to set it up so that the lookup array has the multiple values?
Do you mean the cells in A9:A12 have multiple values, and if any of those values appear in the range A1:B3 to return the relevant match?
Yes - is that possible?
I'll try to think of a way. I'm not promising though!
I think this might not be possible - or at least, there is potential for one lookup value to match to more than one value from the lookup array.
With the above example, suppose A9 was '40 30' the correct result would need to be both 'two' and 'three'...