Conditional IF INDEX MATCH formula
I am trying to make a conditional index match statement where if "Topic A" is selected from a dropdown column called "Tags", the "Technical Lead" column will have Name A populated in it. Otherwise, it will be populated based on this index match:
=INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0))
Looking at some other discussion topics, it looks like I need to follow this pattern, but I don't understand what to put for the first index match (the value_if_true) since I only want to match it to one person's name. How can I complete the middle part of this formula?
=IF( logical_expression , value_if_true , [ value_if_false ])
=IF([Tags]@row = "Topic A", first_sheet_index_match, second_sheet_index_match)
=IF([Tags]@row = "Topic A", first_sheet_index_match, =INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0))
Thank you for any help!
Best Answer
-
I was under the assumption that you needed two INDEX(MATCH())s. If that is the case and the Person A lives on the current sheet then it would be
=IF([Tags]@row = "Topic A", PersonA@row, INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0)))
Did I understand that correctly? If not, where would you be getting the value for Person A? Could you name the columns you need?
Answers
-
When setting the value_if_true and value_if_false you don't have to use the "=" sign.
This should be your formula:
=IF([Tags]@row = "Topic A", INDEX({Technical Lead}, MATCH(Region@row, {Region}, 0)), INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0)))
-
Hi @Emilio Wright ,
Thank you for the response! I tried that formula and get #INVALID REF whenever "Topic A" appears in the Tags column. Any suggestions?
-
Invalid Ref comes up when SS can't find the reference sheet. I added a made-up reference assuming you would change it. I called it {Technical Lead}. You will have to change this reference range to the one you need to check for.
-
@Emilio Wright, right, that makes sense. Do I have to put a reference, though? I only want to match it to one person's name. So if it's Topic A, Person A gets listed in that cell. If it's not Topic A, it uses the second index match where all of the other references are set up.
I guess I could set up a second reference sheet to only list Person A, but if there's a way to just capture that information in the formula, that would be simpler.
-
I was under the assumption that you needed two INDEX(MATCH())s. If that is the case and the Person A lives on the current sheet then it would be
=IF([Tags]@row = "Topic A", PersonA@row, INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0)))
Did I understand that correctly? If not, where would you be getting the value for Person A? Could you name the columns you need?
-
Hi @Emilio Wright,
My attempt at the formula in the original question is based on other answers I saw in the forums, like this one, which says that IF can only be used with two INDEX MATCHs, if I understood it correctly.
However this second formula you provided works perfectly, without a first index match, so maybe I misunderstood that previous post.
Thank you very much for your patience in helping me sort this out!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!