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
Answers
-
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, ""), "")
-
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
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives