SOLVED

New Contributor

Index, Match, Trim?

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

Re: Index, Match, Trim?

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.

Re: Index, Match, Trim?

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

Re: Index, Match, Trim?

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

See 500 Excel Formula Examples for lots of useful examples.