IF/VLOOKUP v.s IF/Index/Match

Options

Hi All,

I currently have the vlookup formula and was wondering if it's possible to convert to IF/Index/Match.

Basically I need to look up an inventory level from another 2 sheets and if the inventory is <=12 and >6 , it will return and trigger a red flag. Below is the if/vlookup formula I have. However, I am not sure how can I set <=12 and >6 both condition in the formula.

=IFERROR(IF(VLOOKUP(Batch@row, {reference another sheet 1}, 4, false) <= 12, 1, ""), IF(VLOOKUP(Batch@row, {reference another sheet 2}, 4, false) <= 12, 1, ""))

I am also curious is IF/index/match will be better?

Thank you

Answers

  • Shimanta Roy
    Shimanta Roy ✭✭✭✭
    Options

    Hello Emi, could you try the following structure to see if that is what you are looking for:


    =IFERROR( IF(AND( INDEX({Column in reference sheet 1 from where you want to pull data}, MATCH(Batch@row, {Column in reference sheet 1 where Batch@row data will be matched}, 0)) <= 12, INDEX({Column in reference sheet 2 from where you want to pull data}, MATCH(Batch@row, {Column in reference sheet 2 where Batch@row data will be matched}, 0)) >6 ), 1, ""), "")

  • Emi
    Emi ✭✭
    Options

    Hi Shimanta,

    Thank you for the response. Is there a way to for both condition <=12 and >6 for the same sheet? Can I do like this the bold part?

    =IFERROR( IF(AND( INDEX({Column in reference sheet 1 from where you want to pull data}, MATCH(Batch@row, {Column in reference sheet 1 where Batch@row data will be matched}, 0)) <= 12 and >6, INDEX({Column in reference sheet 2 from where you want to pull data}, MATCH(Batch@row, {Column in reference sheet 2 where Batch@row data will be matched}, 0)) >6 ), 1, ""), "")

    Thank you

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 05/25/22
    Options

    Hi @Emi

    What about using a COUNTIFS Function instead? You can check the other sheet and count how many rows are  <= 12 and >6 that also match your batch @row. We can do this twice, one for each sheet, and add the results together.

    Then if there are more than 0 rows that meet that criteria across both sheets, raise the flag.

    =IF(COUNTIFS() + COUNTIFS() > 0, 1, 0)

    Try something like:

    =IF(COUNTIFS({Batch Column Sheet 1}, Batch@row, {Number Column Sheet 1}, <=12, {Number Column Sheet 1}), + COUNTIFS({Batch Column Sheet 2}, Batch@row, {Number Column Sheet 2}, <=12, {Number Column Sheet 2}, >6) > 0, 1, 0)

    Cheers,

    Genevieve

  • Emi
    Emi ✭✭
    Options

    Hi Genevieve,

    Thank you for the response. I am not sure if this is what I want. I will try to put some pictures to see if I can explain better.

    This is from sheet A

    This is from sheet B


    I am trying to set a formula on sheet A flag column for trigger the flag when the month on hand for that lot # from sheet B is <=12. This will trigger the both CC13 and DD14 lot; however, I want to exclude the CC13 lot. I am thinking of <=12 and >6 ;therefore, I only trigger Flag for DD14.

    Thank you

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Emi

    Yes, exactly!

    If you only have the one sheet, you'll only need the one COUNTIFS:

    =IF(COUNTIFS({Lot Number Column}, Lot@row, {Month on Hand}, <=12, {Month on Hand}), >6) > 0, 1, 0)

    What this does is it finds how many rows there are in Sheet B that have the Lot number in the cell to the left (so, 1 row), but then it ALSO looks to see if that same row has a value that's <=12 and >6.

    It counts how many rows in Sheet B match that criteria. In your case that would either be 1 row or 0 rows. If no rows meet your criteria then it won't check the flag. But if even 1 row meets your criteria (such as DD14) then it WILL check the flag.

    Let me know if it works for you!

    Cheers,

    Genevieve