SOLVED

Index, Match, Trim?

%3CLINGO-SUB%20id%3D%22lingo-sub-2381668%22%20slang%3D%22en-US%22%3EIndex%2C%20Match%2C%20Trim%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381668%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20I%20have%20been%20struggling%20with%20this%20formula%20for%20days%20now.%20Any%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20sheet1%2C%20column%20D%20I%20have%20a%20list%20of%20values.%26nbsp%3B%3C%2FP%3E%3CP%3ED1%2020375%3C%2FP%3E%3CP%3ED2%20456%3C%2FP%3E%3CP%3ED3%20203755%3C%2FP%3E%3CP%3ED4%201234%3C%2FP%3E%3CP%3ED5%205678%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20sheet2%2C%20column%20A%20I%20have%20a%20different%20list%20of%20values.%3C%2FP%3E%3CP%3EA1%20987%3C%2FP%3E%3CP%3EA2%20654%3C%2FP%3E%3CP%3EA3%2021748%3C%2FP%3E%3CP%3EA4%209876%3C%2FP%3E%3CP%3EA5%205432%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20sheet%202%2C%20column%20C%20I%20have%20a%20list%20of%20stringed%20values%3C%2FP%3E%3CP%3EC1%20123%3B%201233%3B%204245%3B%20155%3B%20854%3C%2FP%3E%3CP%3EC2%20456%3B%204567%3B%203548%3B%206512%3B%207852%3B%2077534%3C%2FP%3E%3CP%3EC3%20203755%3B%20212969%3B%2091664%3B%20202917%3B%20213344%3C%2FP%3E%3CP%3EC4%20625%3B%20789%3B%2099543%3B%205234%3B%207541%3C%2FP%3E%3CP%3EC5%205555%3B%201234%3B%204859%3B%2087354%3B%2095418%3B%2032548%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20WANT%20is%20this%3A%3C%2FP%3E%3CP%3EIf%20the%20value%20in%20sheet1%20column%20D%20matches%20a%20value%20in%20the%20stringed%20values%20in%20sheet2%20column%20C%2C%20return%20the%20corresponding%20value%20from%20sheet2%20column%20A.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%2C%20my%20sheet1%20would%20look%20like%20this%3A%3C%2FP%3E%3CP%3ED1%2020375%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BH1%20%26gt%3Bblank%26lt%3B%3C%2FP%3E%3CP%3ED2%20456%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BH2%20654%3C%2FP%3E%3CP%3ED3%20203755%26nbsp%3B%20%26nbsp%3BH3%2021748%3C%2FP%3E%3CP%3ED4%201234%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BH4%205432%3C%2FP%3E%3CP%3ED5%205678%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BH5%20321%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20I'm%20having%20is%20that%20it's%20not%20returning%20just%20exact%20matches.%20For%20row%201%3A%2020375%20I%20am%20also%20getting%20the%2021748%20return.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHere%20is%20the%20latest%20rendition%20of%20my%20formula%3A%3C%2FP%3E%3CP%3E%3DIFERROR((INDEX('Sheet2'!%24A%3A%24A%2CMATCH(%22*%22%26amp%3BTRIM(%24D1)%26amp%3B%22*%22%2C'Sheet2'!%24C%3A%24C%2C0)%2C1))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20also%20attached%20a%20small%20sample%20of%20the%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2381668%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-2381757%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2C%20Match%2C%20Trim%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381757%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061940%22%20target%3D%22_blank%22%3E%40Makayle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20H1%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(Sheet2!%24A%241%3A%24A%2450%2CMATCH(%22*%22%26amp%3BD1%26amp%3B%22%3B*%22%2CINDEX(Sheet2!%24C%241%3A%24C%2450%26amp%3B%22%3B%22%2C%2C)%2C0))%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdjust%20the%20ranges%20if%20needed%2C%20then%20fill%20down%20to%20the%20last%20used%20row.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381768%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2C%20Match%2C%20Trim%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381768%22%20slang%3D%22en-US%22%3EYay!%20This%20seems%20to%20have%20worked.%20Going%20to%20test%20on%20my%20full%20data%20set%20now.%20THANK%20YOU!%20(Is%20there%20a%20place%20where%20I%20can%20read%2Flearn%20about%20what%20each%20segment%20is%20doing%3F)%3C%2FLINGO-BODY%3E
New Contributor

Hello! I have been struggling with this formula for days now. Any help would be greatly appreciated!

 

In sheet1, column D I have a list of values. 

D1 20375

D2 456

D3 203755

D4 1234

D5 5678

 

In sheet2, column A I have a different list of values.

A1 987

A2 654

A3 21748

A4 9876

A5 5432

 

In sheet 2, column C I have a list of stringed values

C1 123; 1233; 4245; 155; 854

C2 456; 4567; 3548; 6512; 7852; 77534

C3 203755; 212969; 91664; 202917; 213344

C4 625; 789; 99543; 5234; 7541

C5 5555; 1234; 4859; 87354; 95418; 32548

 

What I WANT is this:

If the value in sheet1 column D matches a value in the stringed values in sheet2 column C, return the corresponding value from sheet2 column A.

So, my sheet1 would look like this:

D1 20375     H1 >blank<

D2 456         H2 654

D3 203755   H3 21748

D4 1234       H4 5432

D5 5678       H5 321

 

The problem I'm having is that it's not returning just exact matches. For row 1: 20375 I am also getting the 21748 return. 

Here is the latest rendition of my formula:

=IFERROR((INDEX('Sheet2'!$A:$A,MATCH("*"&TRIM($D1)&"*",'Sheet2'!$C:$C,0),1)),"")

 

I have also attached a small sample of the data.

3 Replies
best response confirmed by Makayle (New Contributor)
Solution

@Makayle 

In H1:

 

=IFERROR(INDEX(Sheet2!$A$1:$A$50,MATCH("*"&D1&";*",INDEX(Sheet2!$C$1:$C$50&";",,),0)),"")

 

Adjust the ranges if needed, then fill down to the last used row.

Yay! This seems to have worked. Going to test on my full data set now. THANK YOU! (Is there a place where I can read/learn about what each segment is doing?)

@Makayle 

Creating such formulas is mostly experience and a lot of experimenting.

See 500 Excel Formula Examples for lots of useful examples.