Forum Discussion

Thendo91's avatar
Thendo91
Copper Contributor
Nov 07, 2023

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 with data which is unfiltered. If I have a corresponding value in column E (description), then return the value in the F column (amount). If the value is not found, return “0”. 

It’s finding the data correctly and returning 0, but not finding the figure if it’s there. Possibly might need to be INDEX or MATCH.

excel
Formulas and Functions

4 Replies

Sort By
  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 07, 2023

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

    • Thendo91's avatar
      Thendo91
      Copper Contributor
      Nov 07, 2023
      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
      • mtarler's avatar
        mtarler
        Silver Contributor
        Nov 07, 2023
        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.
    • Thendo91's avatar
      Thendo91
      Copper Contributor
      Nov 07, 2023
      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?

Resources