Forum Discussion
excel help needed
Need help with an excel problem. I need help with index match.
I want to classify a list of clients as either active or inactive. I have the list of active and inactive in a different workbook. It looks like this.
Active Inactive
A AF
B AG
AC AH
In the work book i am trying to classify the clients it looks like this.
Clients Class
A
Ad
AB
D
I tried to index match it but no luck as it goes across two columns. any ideas?
You can indent up to 7 IFs; you have 4 classes: profit!
IF(COUNTIF(column 1;client)>0;"active";IF(COUNTIF(column 2;client)>0;"onboarding";IF(COUNTIF(column 3;client)>0;"prospect";IF(COUNTIF(column 4;client)>0;"inactive";""))))
[remember to lock columns cells with $ in order to count all cells for the clients in the rows below]
It may exist a more elegant formula for this, I'm sure; but it should work anyway.
edit: looking the final result, I may suggest adding an IF condition in the beginning to check how many times the client is listed: if there is more than 1 client with the same name, it should alert you. If not, if it's 0 leave blank, else check in which column it is.
Is "Class" the active/inactive text?
In that case, you can just check IF there is the name of the client in the left column (then say active), else check if there is in the right one (and say inactive) or leave blank if there is no such valor in any column (if you don't know if active or not).
IF(COUNTIF(left column;client)>0;"active";IF(COUNTIF(right column;client)>0;"inactive";""))
thats a good point, here is the issue. I should have clarified more as there are 4 classes of clients. It goes like this
Active Onboarding Prospect Inactive
You can indent up to 7 IFs; you have 4 classes: profit!
IF(COUNTIF(column 1;client)>0;"active";IF(COUNTIF(column 2;client)>0;"onboarding";IF(COUNTIF(column 3;client)>0;"prospect";IF(COUNTIF(column 4;client)>0;"inactive";""))))
[remember to lock columns cells with $ in order to count all cells for the clients in the rows below]
It may exist a more elegant formula for this, I'm sure; but it should work anyway.
edit: looking the final result, I may suggest adding an IF condition in the beginning to check how many times the client is listed: if there is more than 1 client with the same name, it should alert you. If not, if it's 0 leave blank, else check in which column it is.
