Matching contents from one cell into another and displaying the contents back

[hahmed87]

Hi All,



I have a little bit of a tricky situation. I have two worksheets. Sheet1 contains a file name with claim numbers like below:

File Name <tbody> </tbody>	Date Created <tbody> </tbody>
AAAA File name with additional characters.doc <tbody> </tbody>	20170809 <tbody> </tbody>
BBBB File name with additional characters.doc <tbody> </tbody>	20170809 <tbody> </tbody>
CCCC File name with additional characters.pdf <tbody> </tbody>	20170809 <tbody> </tbody>
DDDD File name with additional characters.doc <tbody> </tbody>	20170809 <tbody> </tbody>
AAAA & EEEE File name with additional characters.pdf <tbody> </tbody>	20170809 <tbody> </tbody>
More Characters here BBBB & CCCC File name with additional characters.doc <tbody> </tbody>	20170809 <tbody> </tbody>

<tbody>
</tbody>

Sheet2 contains claim numbers as below:

Claim <tbody> </tbody>	Data
AAAA <tbody> </tbody>	Data
BBBB <tbody> </tbody>	Data
CCCC <tbody> </tbody>	Data
DDDD <tbody> </tbody>	Data

<tbody>
</tbody>

I would like to know if there are any formulas out there in which I can look for the Claim number is Sheet2 in Sheet1 and it gives me back all the file names that contain that claim number. For example, Claim AAAA from Sheet2 is in 2 file names in Sheet1, so I would like it to return to me

"AAAA File name with additional characters.doc" and "AAAA & EEEE File name with additional characters.pdf" <tbody> </tbody>

<tbody>
</tbody>

Please let me know if any additional information is needed.



Thanks!

 

[jarjarbingie]

Hi All,



I have a little bit of a tricky situation. I have two worksheets. Sheet1 contains a file name with claim numbers like below:

File Name <tbody> </tbody>	Date Created <tbody> </tbody>
AAAA File name with additional characters.doc <tbody> </tbody>	20170809 <tbody> </tbody>
BBBB File name with additional characters.doc <tbody> </tbody>	20170809 <tbody> </tbody>
CCCC File name with additional characters.pdf <tbody> </tbody>	20170809 <tbody> </tbody>
DDDD File name with additional characters.doc <tbody> </tbody>	20170809 <tbody> </tbody>
AAAA & EEEE File name with additional characters.pdf <tbody> </tbody>	20170809 <tbody> </tbody>
More Characters here BBBB & CCCC File name with additional characters.doc <tbody> </tbody>	20170809 <tbody> </tbody>

<tbody>
</tbody>

Sheet2 contains claim numbers as below:

Claim <tbody> </tbody>	Data
AAAA <tbody> </tbody>	Data
BBBB <tbody> </tbody>	Data
CCCC <tbody> </tbody>	Data
DDDD <tbody> </tbody>	Data

<tbody>
</tbody>

I would like to know if there are any formulas out there in which I can look for the Claim number is Sheet2 in Sheet1 and it gives me back all the file names that contain that claim number. For example, Claim AAAA from Sheet2 is in 2 file names in Sheet1, so I would like it to return to me

"AAAA File name with additional characters.doc" and "AAAA & EEEE File name with additional characters.pdf" <tbody> </tbody>

<tbody>
</tbody>

Please let me know if any additional information is needed.



Thanks!

in Sheet2,

B2=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$2:$A$7),""),COLUMN()-COLUMN($A$1))-1),"") press CTRL+SHIFT+ENTER

drag to the right then down

 

[Aladin Akyurek]

In B2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1),COLUMNS($B2:B2))),"")

which should not be affected by adding rows in front of the header row of Sheet1.

 

[hahmed87]

in Sheet2,

B2=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$2:$A$7),""),COLUMN()-COLUMN($A$1))-1),"") press CTRL+SHIFT+ENTER

drag to the right then down

Thank you for the above formula. Since I had more than the 7 claim file names I modified the formula a little bit:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A:$A)),ROW(Sheet1!$A:$A),""),COLUMN()-COLUMN($A1))-1),"")

it brought back a file name, but it wasn't a file name with anything like the claim number at all. For example if the claim number is "01016075", it brought back "C4B25571 LLR.pdf" what could I be doing wrong?

Thank you

 

[hahmed87]

In B2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1),COLUMNS($B2:B2))),"")

which should not be affected by adding rows in front of the header row of Sheet1.

Thank you for the above formula. Since I had more than the 7 claim file names I modified the formula a little bit:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A3,Sheet1!$A:$A)),ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$2)+1),COLUMNS($B3:B3))),"")

it brought back a file name, but it wasn't a file name with anything like the claim number at all. For example if the claim number is "01016075", it brought back "C4B25571 LLR.pdf" what could I be doing wrong?

Thank you

 

[hahmed87]

In B2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$A$2:$A$7,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1),COLUMNS($B2:B2))),"")

which should not be affected by adding rows in front of the header row of Sheet1.

I really appreciate the help. Since I had more than the 7 claim file names I modified the formula a little bit:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A3,Sheet1!$A:$A)),ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$2)+1),COLUMNS($B3:B3))),"")

it brought back a file name, but it wasn't a file name with anything like the claim number at all. For example if the claim number is "01016075", it brought back "C4B25571 LLR.pdf" what could I be doing wrong?

Thank you

 

[jarjarbingie]

Thank you for the above formula. Since I had more than the 7 claim file names I modified the formula a little bit:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A2,Sheet1!$A:$A)),ROW(Sheet1!$A:$A),""),COLUMN()-COLUMN($A1))-1),"")

it brought back a file name, but it wasn't a file name with anything like the claim number at all. For example if the claim number is "01016075", it brought back "C4B25571 LLR.pdf" what could I be doing wrong?

Thank you

try replacing -1 with +1, or try adjusting that number since the ranges got changed..

 

[Aladin Akyurek]

I really appreciate the help. Since I had more than the 7 claim file names I modified the formula a little bit:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(ISNUMBER(SEARCH($A3,Sheet1!$A:$A)),ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$2)+1),COLUMNS($B3:B3))),"")

it brought back a file name, but it wasn't a file name with anything like the claim number at all. For example if the claim number is "01016075", it brought back "C4B25571 LLR.pdf" what could I be doing wrong?

Thank you

Where (in which cell) did you implement the suggested formula?

 

[hahmed87]

Thank you for the idea, I ended up having to put a +2 in the formula:

{=IFERROR(INDEX(Sheet1!$A:$A, LARGE(IF(ISNUMBER(SEARCH($A2,Sheet1!$A:$A)),ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$2)+2),COLUMNS($B2:B2))),"")} this worked for me.

<tbody>
</tbody>

 

[Aladin Akyurek]

Thank you for the idea, I ended up having to put a +2 in the formula:

{=IFERROR(INDEX(Sheet1!$A:$A, LARGE(IF(ISNUMBER(SEARCH($A2,Sheet1!$A:$A)),ROW(Sheet1!$A:$A)-ROW(Sheet1!$A$2)+2),COLUMNS($B2:B2))),"")} this worked for me.

<tbody>
</tbody>

Try to add a row in front of the header row of Sheet1...