Issues to get info from different tables/sheets

[FVDYoshida]

Hi everyone, I hope your all fine.

I am getting crazy with this question (specially because I am not an excel expert).

Table1 (Sheet1)

Character	Power
Green Lantern
SuperMan
Clark Kent
Bruce Wayne
Batman

Table2(sheet2)

Name	Power
Clark	Don´t fly
Bruce	Don´t fly
Bat	Don´t fly
Superman	Fly
Green	Fly

The idea s check Table1 ColumnA against table2 columnA. If there is a partial match

Table1(A1): Green Lantern
Table2(A5):Green

It shows me in Table1(B1):   Fly

I tried Vlookup, Xloopkup, and search with no success ( I mean a lot of errors).

Can someone help me on this case?

Thank you in advance

Fabio

[#2]

@FVDYoshida

For example in B2:

=INDEX(Table2[Power],MATCH(TRUE,ISNUMBER(SEARCH(Table2[Name],[@Character])),0))

(confirmed with Ctrl+Shift+Enter in all versions of Excel), or

=XLOOKUP(TRUE,ISNUMBER(SEARCH(Table2[Name],[@Character])),Table2[Power])

(in Microsoft 365/Office 2021)

[#3]

Hi @Hans Vogelaar, tks for your reply. 

both options worked on my test file, however when I changed to use on my real file I got #VALUE! as result.  Any clues? 

thanks again

Fabio

[#4]

@FVDYoshida 

Could you attach a sample workbook that demonstrates the error?