May 24 2021 12:01 PM
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.
May 24 2021 12:16 PM
SolutionIn 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.
May 24 2021 12:25 PM
May 24 2021 12:41 PM
Creating such formulas is mostly experience and a lot of experimenting.
See 500 Excel Formula Examples for lots of useful examples.
May 24 2021 12:16 PM
SolutionIn 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.