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

Little_Miss_Excel

New Member
Joined
Sep 6, 2017
Messages
2
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You can probably use COUNTIF instead:

Code:
=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
 
Upvote 0
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"))))
 
Upvote 0
You can probably use COUNTIF instead:

Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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