• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Advanced Lookup query

wilson

New Member
Hello


I am having trouble searching for multiple text strings in a list and returning the correct text string if it appears.


I have a reference list as below


Reference List


London

Cardiff

Exeter

Bristol


And a data list


Data List


London

Cardiff City Centre

City of London

Cardiff City Centre

Bristol

ExeterStDavids


And I want to return the correct city in the cell next to the text string so that I get the below result in column B


Column A Rows 1 to 6

London

Cardiff City Centre

City of London

Cardiff City Centre

Bristol

ExeterStDavids


Column B Rows 1 to 6

London

Cardiff

London

Cardiff

Bristol

Exeter


(apologies that I couldn't put this in tabular form - when I make the post it squashes everything together so it looks like I want them all in one cell)


I can do this for a small reference list by using a nested if formula in cell B1 and then copying down


=IF(IFERROR(SEARCH("London",A1),0)>0,"London",IF(IFERROR(SEARCH("Cardiff",A1),0)>0,"Cardiff",IF(IFERROR(SEARCH("Exeter",A1),0)>0,"Exeter",IF(IFERROR(SEARCH("Bristol",A1),0)>0,"Bristol",0))))


But cannot do this if there is 1000 items in the reference list.


As the text match is not exact VLOOKUP won't work and as there is an IF THEN element I am at a loss how to do it, I have tried using array formulas but cannot seem to get this to work as I am not familiar with them

.


I cannot find this on any forums or in the help, but it is difficult to phrase what I am trying to do in a succinct enough way.


Can anyone help with this?


Thanks

Wilson


Thanks
 
Assuming the city list is in M2:M5, try this array formula


=INDEX(M:M,MIN(IF(ISNUMBER(FIND($M$2:$M$5,A2)),ROW($M$2:$M$5))))
 
Back
Top