Column list search

[JRIVERA77]

Need help please!!!! I have 8 columns and 10 items to look up for within these columns. I need a function witch will give me a 1 if any of the the items are in the columns..I have this function to find one item, but when i try to put more items to be found i get no result..

thanks

=IF(OR(S2="BGMFIELDNOTIF",U2="BGMFIELDNOTIF",W2="BGMFIELDNOTIF",Y2="BGMFIELDNOTIF",AA2="BGMFIELDNOTIF",AC2="BGMFIELDNOTIF",AE2="BGMFIELDNOTIF",AG2="BGMFIELDNOTIF"),1, " ")

[sp3cialist]

Not sure I understand fully, have you tried V Lookup?

"JRIVERA77" wrote:

>
> Need help please!!!! I have 8 columns and 10 items to look up for within
> these columns. I need a function witch will give me a 1 if any of the
> the items are in the columns..I have this function to find one item,
> but when i try to put more items to be found i get no
> result..
>
> thanks
>
> =IF(OR(S2="BGMFIELDNOTIF",U2="BGMFIELDNOTIF",W2="BGMFIELDNOTIF",Y2="BGMFIELDNOTIF",AA2="BGMFIELDNOTIF",AC2="BGMFIELDNOTIF",AE2="BGMFIELDNOTIF",AG2="BGMFIELDNOTIF"),1,
> " ")
>
>
> --
> JRIVERA77
> ------------------------------------------------------------------------
> JRIVERA77's Profile: http://www.excelforum.com/member.php...o&userid=31325
> View this thread: http://www.excelforum.com/showthread...hreadid=510111
>
>

[Ron Coderre]

See if this works for you:

=IF(ISNUMBER(SEARCH("BGMFIELDNOTIF",S2&U2&W2&Y2&AA2&AC2&AE2&AG2)),1," ")

That will determine if BGMFIELDNOTIF exists anywhere in those cells.
Note: it will also match if that string is embedded within a cell.
Example: aaaBGMFIELDNOTIFbbb

Otherwise, if you need cell content matches, maybe this with will suit your needs:
=IF(SUMPRODUCT(((S2:AG2)="BGMFIELDNOTIF")*MOD(COLUMN(S2:AG2),2))>0,1," ")

Does that help?

Regards,
Ron