Returning a different value depending in which column a match is found

[Little_Miss_Excel]

I have a list of customers who have downloaded a product in column I of a worksheet.

We categorise existing customers as 'Gold', 'Silver' or 'Bronze' .

In a separate worksheet 'Customer Cats', I have 3 lists of these customers - one 'Gold' column, one 'Silver' and one 'Bronze' column - columns A, B and C respectively

What I want to do is search the list of names of customers who have downloaded the new product against these category lists - ie. if the customer is found in the 'Gold' list, output 'Gold', if 'Silver', output 'Silver', if 'Bronze', output 'Bronze' - and if not at all, output 'New'

I have made a start using IF and MATCH functions.

Where I just input one list to search it works OK e.g.

IF(MATCH(A1,'Customer Cats'!$A:$A,0),"GOLD") - produces "GOLD" if the customer is found in the Gold list or #N/A otherwise

But when I start trying to generate multiple outputs for different lists by nesting if statements, it all goes wrong

Have attempted this - can anyone spot errors in the below or a better way to do it entirely?

IF(MATCH(I1,'Customer Cats'!$A:$A,0),"GOLD", IF(MATCH(I1, 'Customer Cats!$B:$B,0),"SILVER", IF(MATCH(I1, 'Customer Cats'!$C:$C,0), "BRONZE", "NEW")))

Thanks - I am brand new to Excel and this forum so would really appreciate your help; please tell me if you need more info.

 

[wideboydixon]

You can probably use COUNTIF instead:

=IF(COUNTIF('Customer Cats'!$A:$A,I1)>0,"GOLD",IF(COUNTIF('Customer Cats'!$B:$B,I1)>0,"SILVER",IF(COUNTIF('Customer Cats'!$C:$C,I1)>0,"BRONZE","NEW")))

WBD

 

[steve the fish]

You could use IF(ISNUMBER(MATCH( but i think id go:

=IF(I1="","",IF(COUNTIF('Customer Cats'!$A:$A,I1),"GOLD",IF(COUNTIF('Customer Cats'!$B:$B,I1),"SILVER",IF(COUNTIF('Customer Cats'!$C:$C,I1),"BRONZE","NEW"))))

 

[Little_Miss_Excel]

You can probably use COUNTIF instead:

=IF(COUNTIF('Customer Cats'!$A:$A,I1)>0,"GOLD",IF(COUNTIF('Customer Cats'!$B:$B,I1)>0,"SILVER",IF(COUNTIF('Customer Cats'!$C:$C,I1)>0,"BRONZE","NEW")))

WBD

Thanks legend, this worked!

Cheers to all who took time to think this problem through.