Forum Discussion
Help with this "IFS" statement in office 365
Hello Luigi
IFS() is new in Excel 2016 (but only for Office 365 subscribers and in Excel Online).
And for your problem I would suggest a LOOKUP() formula.
Hello Detlef,
I had another go with the look up formula and still don't get it
See attachemtent
Can you give some pointers
May be I am using the wrong type of formula.
What I am trying to achieve is, from a name in a cell (8 possible options) and a number in another cell (9 options) select a value from a range of cells (in one column) to give a value.
Example if there is water in this cell and the number is 3 then select the number 25
=LOOKUP(U31=3,S31="Bones"),(U31=1,S31="Butter”),(U31=1,S31="Cheese"),(U31=1,S31="Grass"),(U31=1,S31="Meat"),(U31=1,S31="Milk"),(U31=1,S31="Water"),(U31=1,S31="Whey"),25
and then I tried this option too.
=MATCH(U31=3,S31=Bones),(U31=1,S31=Butter),(U31=1,S31=Cheese),(U31=1,S31=Grass),(U31=1,S31=Meat),(U31=1,S31=Milk),(U31=1,S31=Water),(U31=1,S31=Whey),25
Please provide the workbook and not a photo of the workbook.
Detlef, I have attached the complete file as it stands now.
There are a numer of complete as well as incomplete components as you will see.
I was starting a slow process of working out each of the formulas.
It has nothing to do with milk or cheese, they were just easy names that did not need any explanation.
The particular formula I was discussing is on the tab called Pay Calculator where the two conditions are in Cell B3 and E8 with formula resulting in cell F8, H8, J8, L8.
So as the name or value changes in B3 or E8 then so will the values in F8, H8, J8, L8 change.
Thanking you for offering to have a closer look at the situation.
Luigi
http://www.OodlesofUtils.com
A Game of Choices and Trade Offs
- Hello Detlef,
I am an Office 365 subscriber.
I will try the Lookup option and let you know.
Would it go like this;
=lookup(E8=1,B3="Whey"),15,AND(E8=1,B3="Milk”),15,AND(E8=1,B3="Cheese"),15,AND(E8=1,B3="Butter"),15,AND(E8=1,B3="Meat"),20,AND(E8=1,B3="Bones"),25,AND(E8=1,B3="Grass"),15,AND(E8=1,B3="Water")
I am an Office 365 subscriber.Then IFS() should work.
For the lookup formula create a helper table in E12:F19 with the products in column E and the values in column F. Column E must be sorted in ascending order.
=LOOKUP(B3,$E$12:$F$19)
- Or CHOOSE()
- Hello Sergei,
Detlef suggested Lookup
I will also give Choose a go
Would this formula go something like this;
=choose(E8=1,B3="Whey"),15,AND(E8=1,B3="Milk”),15,AND(E8=1,B3="Cheese"),15,AND(E8=1,B3="Butter"),15,AND(E8=1,B3="Meat"),20,AND(E8=1,B3="Bones"),25,AND(E8=1,B3="Grass"),15,AND(E8=1,B3="Water")the choose option is not working either
May be I have the formula wrong or a parameter wrong
Here is a sample attached of what I am trying to achieve
