Here is my scenario:
I currently have the following formula in column D (EntityId). It allows me to grab the ID from the MMA Product List sheet if the name in column E (Entity Name) is a match:
=IFERROR(INDEX('MMA Product List'!$A:$A,MATCH(E2, 'MMA Product List'!$B:$B,0)), "Not Found")
MMA Uploader sheet:
MMA Product List sheet:
From above, since both sheets have the Baja Turkey Avocado Sandwich name, it will return the ID from column A MMA Product List sheet to column D EntityId.
Here is my question:
- How can I make my formula dependent on the EntityType in column C? For example, IF column C EntityType is Products, grab the ID from the MMA Products List sheet. IF column C EntityType is Ingredient, grab the ID from the Ingredients List sheet. IF column C EntityType is MenuCategory, grab the ID from the Menu Category sheet.
- The formula is working just fine, so I would just like to know how I can implement an IF statement in that formula.
Thank you!
CodePudding user response：
In your formula where you feed range for INDEX & MATCH you can put an IF statement wrapped in INDIRECT formula e.g.
=IFERROR(INDEX(INDIRECT("Table"&IF(C2="MenuCategory",1,IF(C2="Products",2,IF(C2="Ingredient",3,FALSE)))&"[id]"),MATCH(E2,INDIRECT("Table"&IF(C2="MenuCategory",1,IF(C2="Products",2,IF(C2="Ingredient",3,FALSE)))&"[name]"),0)), "Not Found")
So in my example I created three tables (called Table1, Table2, Table3) with different IDs and then depending on value in C2 INDIRECT serves required range.
CodePudding user response：
tomi09pl posted a great solution, but I decided the method below works best for my case:
=IF(C2="Products", IFERROR(INDEX('MMA Product List'!$A:$A,MATCH(E2, 'MMA Product List'!$B:$B,0)), "Not Found"), IF(C2="MenuCategory", IFERROR(INDEX('Products Category List'!$B:$B,MATCH(E2, 'Products Category List'!$A:$A,0)), "Not Found"), IF(C2="Ingredient", IFERROR(INDEX('Ingredients List'!$A:$A,MATCH(E2, 'Ingredients List'!$B:$B,0)), "Not Found"), "Not Found")))
Thanks to all.