IF/VLOOKUP v.s IF/Index/Match

Emi

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

Shimanta Roy

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

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.

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

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.

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