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
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