Match formula does not work when data is sorted

**steinfm** · 11-22-2011, 08:24 PM

I am using the match formula to identify get a date off of an input sheet.

+INDEX('INPUT SHEET'!B:B,MATCH(A34,'INPUT SHEET'!I:I,0))

The formula itself works perfectly and I get the right results. Because not every line returns a value or some values may be different, I need to sort the results.

My problem is when I sort the results, the match reference cell doesn't adjust to the new sorted position. In other words, if A34 has the match data prior to the sort, but after the sort, the match data is now on A5, my formula doesn't change from +INDEX('INPUT SHEET'!B:B,MATCH(A34,'INPUT SHEET'!I:I,0)) to +INDEX('INPUT SHEET'!B:B,MATCH(A5,'INPUT SHEET'!I:I,0)).

As a result of this, I now get inaccurate results. Does anyone have a suggestion on how to fix this?

**Richard Buttrey** · 11-22-2011, 08:37 PM

Hi,

Please upload your workbook so that we can see the request in context.

Regards

**darkyam** · 11-22-2011, 08:45 PM

There are four ways around this that I can see:
1. (Recommended) When you sort, simply include the column that has this formula. When you sort A, the formulas will be sorted as well.
2. In a cell preferably in a blank row just above your other formulas, (could be in a hidden row if you want), keep a master of the formula. When you're done sorting, highlight this cell down through the end of your formulas and hit Ctrl+D. This will readjust them all.
3. (Not really recommended, but one of two ways to avoid any additional steps every time) Change your formula to =Index('INPUT SHEET'!B:B,MATCH(INDIRECT("A"&ROW()),'INPUT SHEET'!I:I,0)). This is not recommended simply because Indirect is a volatile function and, as such, will make your sheet recalculate over any change and thus slow it down.
4. If you're into setup work obviating the need for additional work later, you can insert two columns after A, perhaps you can work in the attached spreadsheet that I made a while ago for another issue. It takes a list in A and autosorts it in C. Naturally, your formulas would then have to refer to C.