# Show a particular letter/value if a specific cell is included with one of the lists

• Hi

I have been struggling with this for a while and you haven't let me down before!!
I have 5 lists, each with unique values. What I need is if a given cell starts with one of these unique values, indicate which list it comes from?!

I have attached a file for you to look at.

on tab 'All' call 'A3' I need a TW to show in that cell this is because cell 'E3' text 'A CARBON' begins with 'A C' which is included in TW list on tab 'Lists'

## Files

• Re: Show a particular letter/value if a specific cell is included with one of the lis

To simplify, you can add 3 helper columns, one to check 1st 3 characters, then one to check 1st 2 characters, then one to check 1st character.

So in G3:

[COLOR="#0000FF"]=SUMPRODUCT((ISNUMBER(SEARCH("#"&LEFT(E3,3)&"#","#"&Lists!\$A\$2:\$E\$31&"#"))*(COLUMN(Lists!\$A\$2:\$E\$31)-COLUMN(Lists!\$A\$2:\$A\$31)+1)))[/COLOR]

in H3

[COLOR="#0000FF"]=SUMPRODUCT((ISNUMBER(SEARCH("#"&LEFT(E3,2)&"#","#"&Lists!\$A\$2:\$E\$31&"#"))*(COLUMN(Lists!\$A\$2:\$E\$31)-COLUMN(Lists!\$A\$2:\$A\$31)+1)))[/COLOR]

in I3

[COLOR="#0000FF"]=SUMPRODUCT((ISNUMBER(SEARCH("#"&LEFT(E3,2)&"#","#"&Lists!\$A\$2:\$E\$31&"#"))*(COLUMN(Lists!\$A\$2:\$E\$31)-COLUMN(Lists!\$A\$2:\$A\$31)+1)))[/COLOR]

each copied down.

Then in A3 this Array* formula:

[COLOR="#0000FF"]=IF(SUM(G3:I3)=0,"",INDEX(Lists!\$A\$1:\$E\$1,MIN(IF(G3:I3>0,G3:I3))))[/COLOR]

copied down

[arf]*[/arf]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Show a particular letter/value if a specific cell is included with one of the lis

Thank you, you are a star!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!