How to use IFs for multiple Category conditions

[syazaaoa95]

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. 

[#2 · Accepted solution]

@syazaaoa95 

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")

[#3]

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!