Nested MID formula in Index Match?

HI.

I am trying to nest a MID argument into an Index Match formula. Basically, Index match isnt an issue, the returned value is a string of numbers in a cell seperated by "-" for example 1234-56-789-1234 I need to split the returned value into four cells as opposed to returning the complete value. Any suggestions on how i should go about this?

Thanks.

The easiest way would be to use 5 cells instead of 4, with the index / match formula in the first, then the remaning 4 to split it.

With the index formula in A2, try this in B2, then drag right

Formula:

Please Login or Register  to view this content.

Hi Jason.

Thanks, but unfortunately that isnt an option. Im restricted to just the four columns/cells.

In that case, you would need to replace each of the references to $A1 in the formula with your existing index match formula.

All of the column references in the index match parts of the formula must be absolute, rows can be relative if required. If you need more help in combining the formulas, then I will need your index match formula to work with.

Thanks,

formula is:
=IFERROR(INDEX('501'!A:A,MATCH(L618,'501'!B:B,0)),"")

I think that I have this right, I would suggest changing the reference to $B2:B2 (in red) to the cell that you enter the formula into. This is to prevent the formula failing if anyone uses right click - delete.

Formula:

Please Login or Register  to view this content.

The formula uses a lot of exact matches with entire columns, so calculation could be a bit slow. Using smaller ranges in the formula would be advisable here, you don't need to evaluate over 1 million rows of data if you only ever have a maximum of 1000 used.

Works perfectly! Thank you so much, was more complicated than i was expecting though.

That is the downside of not being able to use an extra cell.
If you look back at the shorter version in post #2, you will notice that $A2 is referred to 5 times, without the luxury of that extra cell holding the index match formula, we have to replace each of those 5 references with the full index match formula.

There are ways that the formula could be simplified, but at the risk of sacrificing accuracy, particularly if the number of digits in each part of your number is variable.