Index / Match with random multiple rows

HughT

Board Regular
Joined
Jan 6, 2012
Messages
113
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
How about using Countifs, like
=IF(COUNTIFS(Data!C:C,C2,Data!S:S,"X")>0,"X","")
 
Upvote 0
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.
 
Last edited:
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top