Forum Discussion
Can I do a IF statement that if true, it completes another formula?
=IF(OR(VLOOKUP(A2,Sheet2!A:E,5,FALSE)="Apples",VLOOKUP(A2,Sheet2!A:E,5,FALSE)="Oranges",VLOOKUP(A2,Sheet2!A:E,5,FALSE)="Bananas"),VLOOKUP(A2,Sheet2!A:F,6,FALSE),0) Essentially I have another tab ...
short answer is YES. That should definitely work. I don't know why it isn't working for you based on that example. A slightly improvement might be:
=IF(OR(VLOOKUP(A2,Sheet2!A:E,5,FALSE)={"Apples","Oranges","Bananas"}), VLOOKUP(A2,Sheet2!A:F,6,FALSE),0)
I would also recommend you consider XLOOKUP since it is more efficient (at least that is my understanding and this assumed you have Excel 365).
- Following on from below, as the Yes, I Want My Order Supplied In Cardboard Box and the Apples, Oranges etc. are all in the same column, it's finding the order number (A2) and if the first instance doesn't match up, it's showing '0'. I want it to look for all the possible instances
- Assuming you have 365 then maybe use FILTER so something like this maybe:
=LET(InRange, Sheet2!A:F,
OrderRows, FILTER(InRange, INDEX(InRange,,1)=A2,""),
FindSpecials, FILTER(OrderRows, SUM(--(INDEX(OrderRows,,5)={"Apples","Oranges","Bananas"})),""),
INDEX(FindSpecials, 1, 6))
so LET() allows you to make and use 'local' variables. So this first line sets an input range, then filters based on the 1st column of that range for order number A2, then filters those results based on the 5th column for any of those special terms, then outputs the 6th column. of the first one found (remover the "1" to have a full list of results 'spill')
alternatively in a shorter statement you can combine those steps:
=LET(in, Sheet2!A:F,
INDEX(FILTER(in, (INDEX(in,,1)=A2)*SUM(--(INDEX(in,,5)={"Apples","Oranges","Bananas"})),""),,6))
Hopefully this at least gives you an idea how to use these newer powerful functions.
- Thank you! That looks to have worked. If I had to look up in a further column a term such as "Yes, I Want My Order Supplied In Cardboard Box" and then return a value, I appear to be having the 0 error again. Is it because it's a longer sentence and may need to do a partial match?
