Forum Discussion
How to use IFs for multiple Category conditions
Below, I had attached my excel.
I have problems, where I wanted to use my IFs for my categories. But turns out, I can't use it.
This is my function and it only works for first row only. The other rows, it shows #value :
=IFS(A4=CATEGORY!$B$3:$B$12,"Consumables",A4=CATEGORY!$B$13:$B$26,"Spare Parts")
you can try to see what is my problems.
I want to let it read the categories I had select turns out to be the one I stated there.
It got only two options for my output: Consumables and Spare Parts.
But the category is a lot. So, I think there might be a specific function which I need to add up there in sentence.
Please help. Thank you.
Not sure why you thought of using IFS. Try XLOOKUP like below if your Excel version supports it. But first transform the DATA into a structured table. I just called it Table1.
=XLOOKUP([@CATEGORY],Table1[CATEGORIES],Table1[TYPE OF PRODUCT],"Not found")Alternatively, use INDEX and MATCH to achieve the same result.
=IFERROR(INDEX(Table1[TYPE OF PRODUCT],MATCH([@CATEGORY],Table1[CATEGORIES],0)),"Not found")
2 Replies
Not sure why you thought of using IFS. Try XLOOKUP like below if your Excel version supports it. But first transform the DATA into a structured table. I just called it Table1.
=XLOOKUP([@CATEGORY],Table1[CATEGORIES],Table1[TYPE OF PRODUCT],"Not found")Alternatively, use INDEX and MATCH to achieve the same result.
=IFERROR(INDEX(Table1[TYPE OF PRODUCT],MATCH([@CATEGORY],Table1[CATEGORIES],0)),"Not found")- Hey Riny,
Thank you so much for giving me ideas.
I am still in learning about using excel. Sometimes, I cannot imagine which function is much more suitable for me to use atm.
I don't have XLookuUp. But IndexMatch is the best match for my problems now.
Thank you!
