Multiple If Statements - How to Streamline

Hello All,

New member, and I'm definitely a novice at Excel. Vlookups and statements off of this are about as detailed as I have gotten before, which brings me to my issue.

I have a large spreadsheet that looks up a value, then must go to another sheet to look up and see if the spreadsheet matches 3 criteria in each row. There it takes about 4 hours to calculate with all the inefficiencies. I was wondering if anyone could help clean this up with a better formula. There are 340K of these formulas just like this in the sheets on the spreadsheet...thus the issue.


{=IF(MAX(IF(Staging!$A:$A=$A3,ROW(Staging!$A:$A))*IF(Staging!$B:$B<=KN$1,ROW(Staging!$B:$B))*IF(Staging!$C:$C>=KN$1,ROW(Staging!$C:$C)))>0,"N","Y")}

Staging A:A (1,700 Rows) shows the value that I am trying to match in this sheet's A3 (Call the value in A3 "T7000").
Next it looks to see if the date in Column B (start date) is less than or equal the date in this cell column's header,
Then it looks to see if the date in Column C is greater than or equal to the next column's date in it's header,
Finally, If this is true (if the start and/or finish dates run during or through the above range), then it returns No if it is in the range to show the equipment is shut down for repairs and Yes if it is running (unaffected).


Any help you can provide is great, and thank you!

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

I cannot see the formula in your workbook. Where are the expected results?

Could an item appear more than once on the Staging list?

You should first try limiting the ranges in the formula. Whole column references can cause massive overheads. Turn your staging table into a proper Excel table and then use the table references instead. See if that helps.

=IF(MAX(IF(Table1[Equipment Name]=$A3,ROW(Table1[Equipment Name]))*IF(Table1[Start]<=B$1,ROW(Table1[Start]))*IF(Table1[Finish-Calc]>=B$1,ROW(Table1[Finish-Calc])))>0,"N","Y")

Let us know what happens when you have tried my suggestion, please.

Excellent.

Yes, a VERY bad habit! If you use tables, as I suggested, then your ranges will become dynamic.

Glad to have helped!