IF/VLOOKUP v.s IF/Index/Match

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

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.