match and index function - getting it to keep its original referencecell, in spite of sorting the da

[Mike C]

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.

 

[T. Valko]

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.