Dec 02 2021 10:54 AM
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
Dec 02 2021 11:47 AM
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)
Dec 02 2021 12:38 PM
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
Dec 02 2021 01:07 PM
Could you attach a sample workbook that demonstrates the error?