Vlookup (or other function) to return data based on wildcard Match result

[NFaraci]

Hello,

I have a list of items I'm searching with wildcard against a much larger list, currently to see if there is at least one hit. Now I want to pull data from the first list based on the result, and I can't figure it out. I'll detail below.

	A	B	C	D	E	F	G
1	123 Main	Blue		789 Archer Blvd Apt 1	TRUE
2	456 North	Red		131 Turhune Ave Floor 2	FALSE
3	789 Archer	Green		123 Main St Suite 3000	TRUE
4
5
6
7

<tbody>
</tbody>

At this time, the formula in E:E is an IF statement based on MATCH("*"&A1&"*",D:D,0). E1 is True as a valid Match was found for A3 in D:D, but now I want to return into F1 the value in B3 based on the Match between A3 & D1. Vlookup isn't helping me here because searching for the value of D1 in A:A is not going to return a result, that's why I used a wildcard Match from A to D in the first place.

I hope I didn't explain it poorly, everyone on here is always so helpful.

Thanks in advance.

 

[Marcelo Branco]

Try

F1 copied down
=IFERROR(INDEX(B$1:B$3,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A$1:A$3,D1)),0),0)),"Not found")

M.

 

[NFaraci]

perfect, thank you!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.