Microsoft Excel's IF() Function Lookup

[@SewerDivision]

I am having the same problem in excel 2019. Whatever capacity or material I choose comes back with 0. I have even copied and pasted the formula just to be sure. Would love to know what I am doing incorrectly. Thank you.

[@OfficeNewb]

We would need to be able to see your data and formula.

[@SewerDivision]

=MIN(IF((B3:B21=E4)*(C3:C21>F4),C3:C21)) I am using your resource spreadsheet. I am unable to upload the spreadsheet.

[@SewerDivision]

@@OfficeNewb =MIN(IF((B3:B21=E4)*(C3:C21>F4),C3:C21)) I am using your resource spreadsheet. I am unable to upload the spreadsheet.

[@SeemaSharma-lj3cf]

Very nice 👍

[@OfficeNewb]

Thank you!

[@twarren3685]

I learned something new. I didn't know the * could be used with the IF function. Question: If the user selects a capacity that is in C3:C21, why does the formula not return that number? Ex: Material = Metal, Capacity = 200.

[@twarren3685]

I just answered my own question, change the second logical test operator to >=, (C3:C21>=F4). I've taken several of your courses and you are an awesome instructor! I've learned and utilized lots of advanced functions that have helped streamline and automate several repetitive tasks at work.

[@OfficeNewb]

Great glad you found the solution!

[@bhargavpandya1191]

Can we use AND function instead of * ?

[@OfficeNewb]

Hello - Great question. The AND function will perform a comparison for each individual column but not together. If you select the cell with the formula, go the FORMULAS tab -- EVALUATE FORMULA and step through each formula. You will see the difference in how the * version and the AND version evaluates the criteria.

[@ranjanda11]

I tried this formula but the value comes out as Zero. Can you explain me why it comes?

[@OfficeNewb]

Just to make sure you type this formula? =MIN(IF((B3:B21=E4)*(C3:C21>F4),C3:C21))

[@ranjanda11]

​@@OfficeNewbYes I typed the same formula in the same file that you have provided. Is there any changes do I need to do in the Excel?

[@barbaraam7256]

I don't know if you tried this on Google Sheets, but I tried (I don't have access to Excel) and it also returned as 0. So I tried to break down the formula into smaller parts and see if it was working. It's not working in same way as in the video. I tried the logical validation of the first part "(B3:B21=E4)" and it came back as false. Sheets interpret it as ALL values from B3:B21 must be equal to E4. And that's why this elegant formula doesn't work at Sheets.