Formua to match data

I have Inventory numbers in Sheet "Creditors" in Col F and the inventory numbers are on sheets SLP1, SLP2, SLP3 in Col C


I would like to set up a formula in Sheet "Creditors" in Col AA2 onwards that where the Inventory number matches the inventory number on sheets SLP1, SLP2, SLP3, then the formula to return the sheet name for eg SLP1 , otherwise return "not found"

Eg if inventory numbers in Col F to be matched to sheets SLP1, SLP2, SLP3 in Col C

Inventory Number
HA6HW3096
W7AJ01082
4Z2H01639
4Z2H01674
W7AJ01101
W76J01103
W7AH03049


Your assistance is most appreciated

Try

in AA2

=IFERROR(INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$C$1:$C$200"),$F2)>0),0)),"Not Found")

Sheetlist is named range (SLP1, SLP2, SLP3)

Thanks for the help John


Is the named Range SLP1, SLP2, SLP3 set up on the source sheet "Creditors" ?

Originally Posted by flupsie

Thanks for the help John


Is the named Range SLP1, SLP2, SLP3 set up on the source sheet "Creditors" ?

you can put it this way if you want.

=IFERROR(INDEX({"SLP1","SLP2","SLP3"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"SLP1","SLP2","SLP3"}&"'!$C$1:$C$200"),$F2)>0),0)),"Not Found")

credits to john

Named range can be set up anywhere.