Feb 15, 2010
I've posted this query before, not on this forum, but I don't think the replies I've had so far are going to do what I want. Initially I was looking for a formula, but the suggested pile of nested IFs won't work for the number of conditions. I saw a previous post on here for a VBA macro to search for a text value in a cell against the cell contents of a range and it seemed to do at least the first part of what I wanted. I attempted to manipulate it a little to test its applicability for my own nefarious purposes but for the life of me I can't get it working.
View 3 Replies
This is complicated by the fact that the actual data is commercially confidential, so I can't show you the actual file, but I can fake what I want with two simpler ones. I've attached them to this post. What I want is a fair bit more complex than the other post I found - I want to be able to compare a partial text string from a given cell in a range ('Check Value' in the attached TestBook2 ) against the strings in a range of cells ('Value 1' in TestBook1), and return the corresponding value from 'Test Value' to the corresponding adjacent cell to the tested 'Check Value', with an order of precedence, for example...
Testbook2 contains an entry in C5 of 'a, e, h, z, x, y'. Testbook1 shows that the return for a, b, c, or d is 'moo', for e, f, or g is 'steve' and for g through q is 'fred', all others being no returned value. Moo>steve>fred, so I want the corresponding 'moo, steve. fred or <blank>' cell to contain 'moo'. Conversely, C6 contains 't, u, z' and therefore shouldn't have a value in 'moo, steve, fred or <blank>'. C12 contains 'f, z, s, y, u' and C15 'i, x, z, s', and therefore should display 'steve' and 'fred' respectively.