Search for a given text from Table range and Return value

[MagsinoAS]

I need help on how to get the result in column B from the table range in column D and E. The PRIORITY is to search for a word/string first from column D from the given value in column A, if the value is not present in the column D search in column E but will return the value in Column D.

Looking at the example below, all the values from cell A2:A6 can be found in column D except for cell A7 which can only be found in Column E.

 

[Fluff]

How about
=IFERROR(VLOOKUP(A2,$D$2:$D$6,1,0),IFERROR(INDEX($D$2:$D$6,MATCH(A2,$E$2:$E$3,0)),""))

Please update your account details to show which version of Excel you are using, as this affects which functions you can use.

 

[MagsinoAS]

Thanks for the reply. Here is the result I got after following the formula given. I think this will not work since the vlookup will get the exact match from column D. Let say for cell A2 (Halswell Christchurch), the value in Column D is only Halswell.

What I got so far is using the formula {=IFERROR(INDEX($D$2:$D$7,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$7,A2)),0)),"")}
However, this only work for the first part, I need to get the desired result for cell A7 which can only be found in Column E.

 

[Fluff]

In that case can you please post your sample data to the board using the XL2BB add-in. Images are not much use.

 

[MagsinoAS]

Here it is...

SL Birthday Form.xlsx
	A	B	C	D	E
1	Store	Desired Result		Store	Store Address
2	Halswell Christchurch	Halswell		Halswell	344 Halswell Road Halswell Christchurch New Zealand
3	gore	Gore	Gore	Gore	225 Main South Road Gore Gore New Zealand
4	Barrington Mall	Barrington	Barrington	Barrington	Shop 72 256 Barrington Street Barrington Mall Spreydon Christchurch New Zealand
5	Whangaparaoa	Whangaparaoa	Whangaparaoa	Whangaparaoa	B8 570 Whangaparaoa Road Stanmore Bay Whangaparaoa New Zealand
6	Lunn Ave, Mt. Wellington, Auckland	Lunn Ave	Lunn Ave	Lunn Ave	430c Ellerslie Panmure Highway Mt Wellington Auckland New Zealand
7	Invercargill	Collingwood		Collingwood	44 North Road Waikiwi Invercargill New Zealand
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IFERROR(INDEX($D$2:$D$7,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$7,A2)),0)),"")
C3:C7	C3	=IFERROR(INDEX($D$2:$D$7,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$7,A3)),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Thanks for that, how about
=IFERROR(INDEX($D$2:$D$7,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$7,A2)),0)),IFERROR(INDEX($D$2:$D$7,MATCH(TRUE,ISNUMBER(SEARCH(A2,$E$2:$E$7)),0)),""))

 

[MagsinoAS]

That works. Thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback.