• 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.

Index and Match

vvgiri

New Member
Hi all, i have a large database of Equipment annual Schedule. I want to compare equipment's current status with the desired status as per the schedule when i select the date. and also display " No action" if matching else Display" Changeover" if not matching. I tried using index and match functions, but not getting the results intended. Please help.
 

Attachments

  • Sample data.xlsx
    11.7 KB · Views: 8
I don't know anything about the dates you'll be having in column Q, so for the moment I've put data validation in them being a selection from the dates in row 2.
See attached.
Cell R3 has:
=IF(INDEX($B$3:$M$23,MATCH(O3,$A$3:$A$23,0),MATCH(Q3,$B$2:$M$2,0))=P3,"No action"," changeover")
copy down.
 

Attachments

  • Chandoo53374Sample data.xlsx
    12.4 KB · Views: 6
Thank you very much for the help. My apologies for not being able to communicate my query. What i am looking for is to compare the equipments current status with the selected date from the schedule. when i enter the date in cell Q2 ( Date range is B2:M2),excel should fetch the entire column values for that day, compare with current status and advise me on whether ok or a change over required.
 

Attachments

  • Chandoo53374Sample data.xlsx
    11.9 KB · Views: 6
Which version of Excel?
See attached.
 

Attachments

  • Chandoo53374Sample data.xlsx
    13.1 KB · Views: 12
Thank you very much, it is working now. One more request if not a problem to you. The current status and schedule come from different sources they do not use same terminology. So the if formula IF P3=Q3 , not working in some cases. some times in the schedule STOP-MECH, STOP-ELEC is used for STOP,and RUNNIG is used for RUN. In this how to use IF formula using more than one arguements? i mean if STOP-ELEC, STOP-MECH, STOPPED is same STOP hence no action required. Similarly RUNNING and RUN is the same . " changeoevr" should appear only earlier mentioned conditions
are not meeting. Hope you understand my querry.
 
Will the first 3 characters of all STOP cells be STO?
Will the first 3 characters of all RUN cells be RUN?
Will they always be upper case?
If so:
=IF(LEFT(Q3,3)=LEFT(P3,3),"No action","Changeover")
 
Yes brother, it is working . I am struggling since very long time to find solution to my problem . Thanks once again for the help.
 
Back
Top