SOLVED
Home

How to Match Certain Char and Return a Value from another Table based on the Matched Char?

%3CLINGO-SUB%20id%3D%22lingo-sub-876238%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Match%20Certain%20Char%20and%20Return%20a%20Value%20from%20another%20Table%20based%20on%20the%20Matched%20Char%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876238%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408917%22%20target%3D%22_blank%22%3E%40PLG_Eng%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20not%20upload%20a%20small%20sample%20file%20with%20some%20dummy%20data%20along%20with%20the%20desired%20output%20mocked%20up%20manually%20to%20show%20the%20end%20result%20you%20are%20trying%20to%20achieve%3F%20That%20would%20help%20to%20visualize%20the%20desired%20output%20and%20to%20provide%20an%20accurate%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876228%22%20slang%3D%22en-US%22%3EHow%20to%20Match%20Certain%20Char%20and%20Return%20a%20Value%20from%20another%20Table%20based%20on%20the%20Matched%20Char%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876228%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%3CEM%3EUpdated%20with%20attachment%20for%20demo%20of%20the%20problem%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Excel%20Experts%20Community%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20one%20list%2C%20OLA%20is%20repeated%20in%20several%20cells%20(under%201%20column)%20but%20has%20no%20fixed%20position%2C%20so%20it%20comes%20in%20different%20positions%20in%20every%20cell%20(e.g.%2C%20OLA-12%2C%2022-OLA%2C%20PE-OLA-00%2C%20etc.)%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%2C%20in%20another%20table%2C%20I%20have%20two%20columns.%20Under%20the%20first%20Column%2C%20exactly%20OLA%20is%20listed%20in%20one%20cell%20along%20with%20another%20value%20next%20it%20to%20under%20the%20second%20column.%20The%20second%20table%20has%20different%20values%20under%20the%20two%20columns%2C%20so%20OLA%20is%20only%20in%20row.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20match%20OLA%20only%20from%20the%201st%20list%20and%20return%20the%20value%20next%20to%20the%20fixed%20string%2C%20OLA%20from%20the%20second%20table%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20WOULD%20BE%20so%20grateful%20for%20your%20support%20to%20solve%20this%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-876228%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876292%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Match%20Certain%20Char%20and%20Return%20a%20Value%20from%20another%20Table%20based%20on%20the%20Matched%20Char%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876292%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20response.%20I%20have%20just%20updated%20the%20thread%20with%20a%20file.%20Please%2C%20have%20a%20look.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876365%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Match%20Certain%20Char%20and%20Return%20a%20Value%20from%20another%20Table%20based%20on%20the%20Matched%20Char%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876365%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408917%22%20target%3D%22_blank%22%3E%40PLG_Eng%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20something%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnmerge%20the%20cell%20B4%20and%20then%20try%20this...%3C%2FP%3E%3CP%3EIn%20B4%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOOKUP(2%2C1%2F(ISNUMBER(SEARCH(TRIM(Table1%5B%5BClean%20Code%20%5D%5D)%2CTable2%5B%40CODE%5D)))%2CTable1%5BValue%5D)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20then%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20this%20is%20what%20you%20were%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876462%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Match%20Certain%20Char%20and%20Return%20a%20Value%20from%20another%20Table%20based%20on%20the%20Matched%20Char%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%2C%20it%20does%20work%20perfectly.%20Could%20you%20please%20re-write%20the%20formula%20using%20cell%20references%20instead%20of%20column%20names%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You--%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876495%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Match%20Certain%20Char%20and%20Return%20a%20Value%20from%20another%20Table%20based%20on%20the%20Matched%20Char%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876495%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408917%22%20target%3D%22_blank%22%3E%40PLG_Eng%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20requested%20formula...%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOOKUP(2%2C1%2F(ISNUMBER(SEARCH(TRIM(%24F%244%3A%24F%246)%2CA4)))%2C%24G%244%3A%24G%246)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879266%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Match%20Certain%20Char%20and%20Return%20a%20Value%20from%20another%20Table%20based%20on%20the%20Matched%20Char%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879266%22%20slang%3D%22en-US%22%3E%3CP%3EDear%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%2C%20indeed.%20I%20truly%20appreciate%20it.%20You%20really%20really%20saved%20my%20day!!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879333%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Match%20Certain%20Char%20and%20Return%20a%20Value%20from%20another%20Table%20based%20on%20the%20Matched%20Char%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879333%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408917%22%20target%3D%22_blank%22%3E%40PLG_Eng%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
PLG_Eng
New Contributor

Updated with attachment for demo of the problem

 

 

 

Hi Excel Experts Community,

In one list, OLA is repeated in several cells (under 1 column) but has no fixed position, so it comes in different positions in every cell (e.g., OLA-12, 22-OLA, PE-OLA-00, etc.)

And, in another table, I have two columns. Under the first Column, exactly OLA is listed in one cell along with another value next it to under the second column. The second table has different values under the two columns, so OLA is only in row. 

How can I match OLA only from the 1st list and return the value next to the fixed string, OLA from the second table?


I WOULD BE so grateful for your support to solve this problem.

7 Replies
Highlighted

@PLG_Eng 

Why not upload a small sample file with some dummy data along with the desired output mocked up manually to show the end result you are trying to achieve? That would help to visualize the desired output and to provide an accurate solution.

Highlighted

@Subodh_Tiwari_sktneer 

 

Thank you very much for your response. I have just updated the thread with a file. Please, have a look. 

 

 

Highlighted
Solution

@PLG_Eng 

You may try something like this...

 

Unmerge the cell B4 and then try this...

In B4

=LOOKUP(2,1/(ISNUMBER(SEARCH(TRIM(Table1[[Clean Code ]]),Table2[@CODE]))),Table1[Value])

and then copy it down.

 

Let me know if this is what you were trying to achieve.

 

Highlighted

@Subodh_Tiwari_sktneer 

 

Thank you very much, it does work perfectly. Could you please re-write the formula using cell references instead of column names? 

 

 

Thank You--

 

Highlighted

@PLG_Eng 

You're welcome! Glad it worked as desired.

 

Here is the requested formula...

=LOOKUP(2,1/(ISNUMBER(SEARCH(TRIM($F$4:$F$6),A4))),$G$4:$G$6)

Highlighted

Dear @Subodh_Tiwari_sktneer,

 

Thank you very much, indeed. I truly appreciate it. You really really saved my day!!  

 

 

 

 

 

Highlighted
Related Conversations
Counting Days
Tim Hunter in SQL Server on
2 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
PWA fullscreen like IE11 kiosk mode
rogihee in Discussions on
5 Replies
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies