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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123 Main[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD]789 Archer Blvd Apt 1[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]456 North[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]131 Turhune Ave Floor 2[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]789 Archer[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD]123 Main St Suite 3000[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.