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.
Index and Match
- Thread starter vvgiri
- Start date
p45cal
Well-Known Member
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.
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.
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.
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.
are not meeting. Hope you understand my querry.