Index Match Match Failure

Geoff Baker

Hi, I am having trouble with an INDEX, MATCH, MATCH formula.

Basically what I want to do is say.

If the PROCUREMENT COUNTRY and the DIV @ROW match then return the PROCUREMENT MANAGER from the DIVISION ASSIGNMENT BREAKDOWN sheet.

=INDEX({DIVISION ASSIGNMENT BREAKDOWN Range 2}, MATCH([PROCUREMENT COUNTRY]@row, {DIVISION ASSIGNMENT BREAKDOWN Range 1}, 0), MATCH(DIV@row, {DIVISION ASSIGNMENT BREAKDOWN Range 3}, 0))

It works up until I add the second match then it returns an #INVALID VALUE

Any help would be greatly appreciated.

Mark Cronk

Ooops. I see what I did. Try JOIN instead. If there's only 1 match in the range it should work for you:

=JOIN(COLLECT({DIVISION ASSIGNMENT BREAKDOWN Range 2}, {DIVISION ASSIGNMENT BREAKDOWN Range 1},@cell=[PROCUREMENT COUNTRY]@row, {DIVISION ASSIGNMENT BREAKDOWN Range 3}, @cell=DIV@row)

Any luck this time?

Mark

Mark Cronk

Hi Geoff,

Try INDEX(COLLECT instead.

=INDEX(COLLECT({DIVISION ASSIGNMENT BREAKDOWN Range 2}, {DIVISION ASSIGNMENT BREAKDOWN Range 1},[PROCUREMENT COUNTRY]@row, {DIVISION ASSIGNMENT BREAKDOWN Range 3}, DIV@row)

Work for you?

Mark

Geoff Baker

Hi Mark I seem to be getting this error: #INCORRECT ARGUMENT SET

Mark Cronk

Ooops. I see what I did. Try JOIN instead. If there's only 1 match in the range it should work for you:

=JOIN(COLLECT({DIVISION ASSIGNMENT BREAKDOWN Range 2}, {DIVISION ASSIGNMENT BREAKDOWN Range 1},@cell=[PROCUREMENT COUNTRY]@row, {DIVISION ASSIGNMENT BREAKDOWN Range 3}, @cell=DIV@row)

Any luck this time?

Mark

Geoff Baker

Mark THANK YOU so much for your help with this. Made my week as I get to tick it off the TODO list!

Mark Cronk

So it worked or you found your own solution. Either way, perfect! Appreciate you contributing to the Community.

Mark

Geoff Baker

Mark your revised solution worked perfectly :)

Alex Knisely

@Mark Cronk - looks like you were able to provide some excellent help here. I have a similar situation I am hoping you can help me with?

I am trying to do a cross-sheet formula to look at two columns 1) ProductType and 2) Productsub-group. If the details match in one, I want to populate details from a 3rd column 'Min.Enrollment'.

I've tried IF, VLOOKUP, and INDEX/MATCH and can't get them to work. Any suggestions?

Thanks,

Alex

Andrée Starå

Hi @Alex Knisely

I hope you're well and safe!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!