Issues to get info from different tables/sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-3028761%22%20slang%3D%22en-US%22%3EIssues%20to%20get%20info%20from%20different%20tables%2Fsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3028761%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20I%20hope%20your%20all%20fine.%3C%2FP%3E%3CP%3EI%20am%20getting%20crazy%20with%20this%20question%20(specially%20because%20I%20am%20not%20an%20excel%20expert).%3C%2FP%3E%3CP%3ETable1%20(Sheet1)%3C%2FP%3E%3CTABLE%20width%3D%22251%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22151%22%3E%3CP%3E%3CSTRONG%3ECharacter%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22100%22%3E%3CP%3E%3CSTRONG%3EPower%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22151%22%3E%3CP%3EGreen%20Lantern%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22100%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22151%22%3E%3CP%3ESuperMan%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22100%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22151%22%3E%3CP%3EClark%20Kent%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22100%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22151%22%3E%3CP%3EBruce%20Wayne%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22100%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22151%22%3E%3CP%3EBatman%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22100%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable2(sheet2)%3C%2FP%3E%3CTABLE%20width%3D%22199%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22135%22%3E%3CP%3E%3CSTRONG%3EName%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CP%3E%3CSTRONG%3EPower%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22135%22%3E%3CP%3EClark%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CP%3EDon%C2%B4t%20fly%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22135%22%3E%3CP%3EBruce%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CP%3EDon%C2%B4t%20fly%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22135%22%3E%3CP%3EBat%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CP%3EDon%C2%B4t%20fly%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22135%22%3E%3CP%3ESuperman%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CP%3EFly%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22135%22%3E%3CP%3EGreen%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CP%3EFly%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20idea%20s%20check%20Table1%20ColumnA%20against%20table2%20columnA.%20If%20there%20is%20a%20partial%20match%3C%2FP%3E%3CP%3ETable1(A1)%3A%20Green%20Lantern%3CBR%20%2F%3ETable2(A5)%3AGreen%3C%2FP%3E%3CP%3EIt%20shows%20me%20in%20Table1(B1)%3A%26nbsp%3B%26nbsp%3B%20Fly%3C%2FP%3E%3CP%3EI%20tried%20Vlookup%2C%20Xloopkup%2C%20and%20search%20with%20no%20success%20(%20I%20mean%20a%20lot%20of%20errors).%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20on%20this%20case%3F%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3CP%3EFabio%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3028761%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3028934%22%20slang%3D%22en-US%22%3ERe%3A%20Issues%20to%20get%20info%20from%20different%20tables%2Fsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3028934%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F623381%22%20target%3D%22_blank%22%3E%40FVDYoshida%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EFor%20example%20in%20B2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(Table2%5BPower%5D%2CMATCH(TRUE%2CISNUMBER(SEARCH(Table2%5BName%5D%2C%5B%40Character%5D))%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E(confirmed%20with%20Ctrl%2BShift%2BEnter%20in%20all%20versions%20of%20Excel)%2C%20or%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DXLOOKUP(TRUE%2CISNUMBER(SEARCH(Table2%5BName%5D%2C%5B%40Character%5D))%2CTable2%5BPower%5D)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E(in%20Microsoft%20365%2FOffice%202021)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies

@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)

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

@FVDYoshida 

Could you attach a sample workbook that demonstrates the error?