I think I'm on the right track but I can't seem to figure this out.

We have an inventory of bus parts and I want to be able to figure out
how many series of buses a part fits.

I'm trying in Column Y to have an IF statement which will look across
from ColJ to ColX and if the cell has a Y in it, then it fits the
series.

Bus Series
ColG ColJ ColK ColL...to ColX ColY

7551 7586 7601... 8066
7585 7591 7644... 8095
Part#
102665 Y Y Y
102862 Y
101164 Y Y Y... Y

Formula in ColY =IF(AND(J5="Y",K5="Y",L5="Y",M5="Y")=TRUE,($J$2& "-"&$M
$3),($J$2& "-"&$L$3))

So for;
part# 102665 in ColY should read 7551-7644
part# 102862 in ColY should read 7551-7585
part# 101164 in ColY should read 7551-8095

Suggestion/Thoughts ???

Dale

On Sep 19, 4:41*pm, lain...@canada.com wrote:
> I think I'm on the right track but I can't seem to figure this out.
>
> We have an inventory of bus parts and I want to be able to figure out
> how many series of buses a part fits.
>
> I'm trying in Column Y to have an IF statement which will look across
> from ColJ to ColX and if the cell has a Y in it, then it fits the
> series.
>
> * * * * Bus Series
> ColG * *ColJ * *ColK * *ColL...to ColX *ColY
>
> * * * * 7551 * *7586 * *7601... 8066
> * * * * 7585 * *7591 * *7644... 8095
> Part#
> 102665 *Y * * * Y * * * Y
> 102862 *Y
> 101164 *Y * * * Y * * * Y... * *Y
>
> Formula in ColY =IF(AND(J5="Y",K5="Y",L5="Y",M5="Y")=TRUE,($J$2& "-"&$M
> $3),($J$2& "-"&$L$3))
>
> So for;
> part# 102665 in ColY should read 7551-7644
> part# 102862 in ColY should read 7551-7585
> part# 101164 in ColY should read 7551-8095
>
> Suggestion/Thoughts ???
>
> Dale

Hard to tell your layout from this so
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

This might do it for you
In Y5: =IF(J5="","",$J$2&"-"&INDEX($J$3:$M$3,COUNTIF(J5:M5,"Y")))
Copy down