match and index function - getting it to keep its original reference cell, in spite of sorting the data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike C - 11 Mar 2008 05:37 GMT

I am using the match and index function to pull in data from a
separate worksheet. I am using Column A as the reference cell,
whereas my formula lies in Column F.

The formula pulls in the data fine. The problem is, when I sort the
data set, the match index function maintains the original reference
cell and does NOT refer to the same row.

So, for example, if my formula references A3, the formula continues to
use A3 after I sort the cell (as if I had used an absolute
reference).

Does anyone know how to fix this? How can I pull in data using
matchindex (or vlookup), but also be able to sort the rows without
messing the formula up? Is there a way to use a list or named ranges
to accomplish this?

The actual formula I am using (in case it helps) is:

=IF(ISNA(INDEX(CMS2!I:I,MATCH('CPT Summary for CMS Comp'!A27,CMS2!
A:A,FALSE))),0,INDEX(CMS2!I:I,MATCH('CPT Summary for CMS Comp'!
A27,CMS2!A:A,FALSE)))

Thanks for any suggestions.

Reply to this Message

T. Valko - 11 Mar 2008 07:33 GMT

>MATCH('CPT Summary for CMS Comp'!A27....

So this particular formula is in F27?

Try one of these. Either will *always* refer to the row the formula is on:

MATCH(INDEX('CPT Summary for CMS Comp'!A:A,ROW())....

MATCH(INDIRECT("'CPT Summary for CMS Comp'!A"&ROW())....

If those formulas are in F27 and you sort your data they will still refer to
A27 after the sort.

Note that you need to change your formula in 2 places.

Signature

Biff
Microsoft Excel MVP

>I am using the match and index function to pull in data from a
> separate worksheet. I am using Column A as the reference cell,
[quoted text clipped - 20 lines]
>
> Thanks for any suggestions.

Reply to this Message