INDEX MATCH Formula

JudeG

Hi guys,

I have created a sign up sheet where individual participants can sign up to discuss with different groups in three consecutive slots. In order to sign up, they need to enter their full name into a cell. I am using the following formula to determine for which discussion a participant is signed up for in each slot:

=INDEX([Primary Column]2:[Primary Column]9, MATCH([Primary Column]@row, [SLOT 1 - participant 1]2:[SLOT 1 - participant 2]9, 0))

I can see that it is looking for the right thing because it says "no match" for Maria Carey in Slot 3, but I don't understand why it is returning completely random rows for all the others... or sometimes even "invalid value"... I am desperately in need of some help.

Best

Judit

David Tutwiler

I think so, but early warning that this might create a pretty long formula depending on how many columns you have.

I think the idea is to try each possible column one at a time within your formula, and if you don't get a result then you move to the next column. The best way I could think of to do this was by wrapping the formula for finding the result in the first column with an IFERROR. That way, if the index is not successful in finding a result in the first column, then it branches to the IFERROR where we tell it to search in the second column. If it is successful in finding a result in the first column, then it simply returns the first result. Here is what I came up with that seems to be working, with a screenshot below the formula.

=IFERROR(INDEX([Primary Column]$3:[SLOT 1 - participant 1]$9, MATCH([Primary Column]@row, [SLOT 1 - participant 1]$3:[SLOT 1 - participant 1]$9, 0), 1), INDEX([Primary Column]$3:[SLOT 1 - participant 2]$9, MATCH([Primary Column]@row, [SLOT 1 - participant 2]$3:[SLOT 1 - participant 2]$9, 0), 1))

David Tutwiler

I will admit that this took some thinking because everything you had written looked logically correct. However, I do believe I have found where the formula is breaking down. I recreated your sheet and was getting the same odd results on your formula you had in place. As a test, I moved all of your entries into a single column (SLOT 1- participant 1) and changed the range to only look at the 1 column, and everything worked as it should. Here is what I think is happening:

When you call MATCH it is returning a relative position that it finds the match in. For a 1-dimension array (a column) it will return the position that it found the result (this will look like a row number). However, for a 2-dimension array (2 columns), I think it is returning the relative position that it finds the value.

This is why, in your example, Johnny Cash shows in Group 3. Because it isn't at "Row 1" it is at "Row 1 and Column 2" which is likely the value 3. So the extra column is throwing off your calculations.

Smarter people on here might be able to come up with a mathematical way for you to be able to account for this, but I would ask if there was any way you could simplify your sheet to use 1 column instead of 2. I have also attached an example screenshot below with the formula for making this work in 1 column.

=INDEX([Primary Column]$3:[SLOT 1 - participant 1]$9, MATCH([Primary Column]@row, [SLOT 1 - participant 1]$3:[SLOT 1 - participant 1]$9, 0), 1)

JudeG

Dear David,

Thank you so much for taking the time. I'm afraid that using just one column wouldn't be very user-friendly (I have many more rows and columns than in this example, so we would end up with a long long list of rows), and I am not sure how I could fit all three slots in there... using a separate table for each slot could be an option but even then, the scroll-down would be endless....

Might there be another formula more suitable for the purpose?

Thanks anyways, David, it is comforting to know that I wasn't utterly wrong in my approach...

Cheers

Judit

David Tutwiler

I think so, but early warning that this might create a pretty long formula depending on how many columns you have.

I think the idea is to try each possible column one at a time within your formula, and if you don't get a result then you move to the next column. The best way I could think of to do this was by wrapping the formula for finding the result in the first column with an IFERROR. That way, if the index is not successful in finding a result in the first column, then it branches to the IFERROR where we tell it to search in the second column. If it is successful in finding a result in the first column, then it simply returns the first result. Here is what I came up with that seems to be working, with a screenshot below the formula.

=IFERROR(INDEX([Primary Column]$3:[SLOT 1 - participant 1]$9, MATCH([Primary Column]@row, [SLOT 1 - participant 1]$3:[SLOT 1 - participant 1]$9, 0), 1), INDEX([Primary Column]$3:[SLOT 1 - participant 2]$9, MATCH([Primary Column]@row, [SLOT 1 - participant 2]$3:[SLOT 1 - participant 2]$9, 0), 1))

JudeG

Oh gosh, David, you are a real pro. Thank you so much. Super helpful!

David Tutwiler

No problem. Glad it got you to where you needed.