• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need to find the status based on multiple criteria,Attached file will tell all.Thanks for the help

B

brijendraydv

Member
In the attached file i need to find the output in status column(I) based on criteria tab.
I have tried explaining the logic in column I.

Please let me know for any clarification if I'm not clear.

Thanks for your support.
 

Attachments

  • Chandoo-query.xlsx
    888.8 KB · Views: 5
Hi ,

I do not understand why you have mentioned the criteria ; should the output be based on the table that is given in the criteria tab ?

Do you want a formula for what is already displayed in column H ?

If yes , then try this :

=VLOOKUP(E2,criteria!$A$2:$B$9,2,FALSE)

Narayan
 
Hi Narayan,

Thanks, let me try to explain-
In tab "Conflict Minerals BU-CL Part" in column "status"(I) i need to have a formula to calculate Responded and not Responded supplier Id(col B).
The criteria is- if in tab "Conflict Minerals BU-CL Part" column "Validation status has either of these values - 'BLANK' or 'Incomplete' or 'Conflict Status Undeterminable' then its Non Responsive supplier (and should have no other status except these 3 for that supplier to be non responsive).

However,if a supplier id has all or either of the above mentioned values but also has values like 'Out of Scope by Supplier' or 'Conflict Free Scrap/Recycler' or 'Conflict Free' or 'Conflict Free - GRM (Geographical Risk Mapping)' or 'Manual Validation' then final Status of that Supplier is not Non Responsive but is "Responsive supplier".

I'm trying to solve but seems tricky (can we use any array formula by giving above conditions).

Thanks,
 
Hi ,

Can you confirm the following ?

1. If the Validation Status column has any of the following values :

Out of Scope by Supplier
Conflict Free Scrap/Recycler
Conflict Free
Conflict Free - GRM (Geographical Risk Mapping)
Manual Validation

then the Status will be Responsive supplier.

2. If the Validation Status column has any of the following values :

BLANK
Incomplete
Conflict Status Undeterminable

then if the Validation Status column has no other values , then the Status will be Non Responsive supplier.

Thus , rule #1 will have priority over rule #2.

Narayan
 
You must log in or register to reply here.
Back
Top