Index / Match with random multiple rows

[HughT]

I have a worksheet called Data. Each row relates to a customer repair. In column C is a part number (3 digit). There are many different part numbers distributed on different rows. If the customer rings to chase the repair the user puts an X in column S against that customer, so there can be many Xs in column S against many part numbers, but not all rows will have an X in column S.

There is a separate worksheet called Repair Log. This lists each part number in column C. In column M of this worksheet I want to an X to appear if there has been a single X entered in any row on the Data worksheet for that part number, ie there is immediate visibility that one chaser call has been made irrespective of which customer it was.

I tried VLOOKUP but that only finds the first row and stops. It looks like Index / Match might work but I have got stuck!

Many thanks

HT

 

[Fluff]

How about using Countifs, like
=IF(COUNTIFS(Data!C:C,C2,Data!S:S,"X")>0,"X","")

 

[Special-K99]

Try

in 'Repair Log'!M1
=IF(COUNTIFS(Data!C$1:C$1000,C1,Data!S$1:S$1000,"X")>0,"X","")
and copy down the column

So for each row in 'Repair Log' column M
count the number of Xs in column S of Data sheet where the part number matches the code in column C
If this result is greater than zero then there is at least one X in column S for the part number mentioned on the Repair Log for that row.

 

[HughT]

Thank you so much!

Works perfectly!

HT

 

[Fluff]

Glad we could help & thanks for the feedback