I have extracted some data from a database and have a situation where I want to display the vehicle franchises. Sometimes I have to display multiple franchises, so I have added that combination to the lookup table, however on some occasions it is not working...
Can anyone give me a reason why I can't get this to work? I have attached an example below. The main error seems to be in cell C4!
Thanks for the help
What result do you expect in C4?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
i think you need to sort your lookup list by length smallest to largest else lookup will return the last match which the way you have it laid out is KIA
see attached
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
The answer that's in C25
Brilliant. Never realised that the lookup table had to be sorted
look up like that returns the last value where there is a match kia matches and is last in the list
You have some kind of "junk" in cell B4.
I added the CLEAN function to the formula in C4 and it returned the correct result.
=IFERROR(LOOKUP(2^15,SEARCH(" "&$C$12:$C$27&" "," "&CLEAN(B4)&" "),$C$12:$C$27)...
The "junk" in B4 is used to remove the ASCII codes that inhibit me from being able to do the lookup...
Thanks for the help. I'll have a look
Good deal. Thanks for the feedback!
it still has to be sorted tony, longest string last ,evaluate attached
It depends on whether there are similar keywords/phrases being looked up.Originally Posted by martindwilson
In this case there are many similarities so the order of the keywords/phrases is critical.
agreed if there is no overlapping fine but its easy to overlook!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks