Index, Match and filters

I have a worksheet where I use the combination of the index & Match function
to retrieve data from other sheets in a workbook. The retrieved data is in
multiple rows and mltiple columns. Each column has autofilter enabled so I
can quickly sort for the rows with specific data. With Office 2003 the
autofilter also allows for quick sortation of the data. My problem is that
within the index match formula the match lookup value does not update row
number as the filter sorting is applied. In other words, if the original
match formula was referencing a lookup value in row 48 and that row changed
as a result of the sort to row 100 the formula still is referencing row 48
instead of the new row 100.

Does anyone know of a work arround? Is there a "trick" I am unaware of?
Thanks for any assistance.

Try referencing entire columns in your index() and match() functions, using
syntax like B:B instead of specific rows like B1:B48

HTH,

TK

"Steve M" wrote:

> I have a worksheet where I use the combination of the index & Match function
> to retrieve data from other sheets in a workbook. The retrieved data is in
> multiple rows and mltiple columns. Each column has autofilter enabled so I
> can quickly sort for the rows with specific data. With Office 2003 the
> autofilter also allows for quick sortation of the data. My problem is that
> within the index match formula the match lookup value does not update row
> number as the filter sorting is applied. In other words, if the original
> match formula was referencing a lookup value in row 48 and that row changed
> as a result of the sort to row 100 the formula still is referencing row 48
> instead of the new row 100.
>
> Does anyone know of a work arround? Is there a "trick" I am unaware of?
> Thanks for any assistance.

I believe I have tried your suggestion. Here is more of what I have.

Formula initially in row 5 column B..

= Index(F:F, Match( C5, G:G, 0))

after a sort row 5 is now row 10 but the formula remains

= Index(F:F, Match( C5, G:G, 0))

instead of

= Index(F:F, Match( C10, G:G,0))

"T Kirtley" wrote:

> Try referencing entire columns in your index() and match() functions, using
> syntax like B:B instead of specific rows like B1:B48
>
> HTH,
>
> TK
>
> "Steve M" wrote:
>
> > I have a worksheet where I use the combination of the index & Match function
> > to retrieve data from other sheets in a workbook. The retrieved data is in
> > multiple rows and mltiple columns. Each column has autofilter enabled so I
> > can quickly sort for the rows with specific data. With Office 2003 the
> > autofilter also allows for quick sortation of the data. My problem is that
> > within the index match formula the match lookup value does not update row
> > number as the filter sorting is applied. In other words, if the original
> > match formula was referencing a lookup value in row 48 and that row changed
> > as a result of the sort to row 100 the formula still is referencing row 48
> > instead of the new row 100.
> >
> > Does anyone know of a work arround? Is there a "trick" I am unaware of?
> > Thanks for any assistance.

By chance, is there a sheet reference in front of the "Lookup_Value" of the
Match() formula? (I am presuming that this is on the same page as your
Index(Match)) formulas).

If you create a formula by paging back and forth among the worksheets in the
workbook, Excel will put a sheet reference in for each cell reference of the
formula you create... even references to the sheet the formula is on. Thus a
formula would look like =INDEX(Data!B:B,MATCH(Sheet1!A1,Data!A:A,0)) instead
of =INDEX(Data!B:B,MATCH(A1,Data!A:A,0)). Although the unnecessary sheet
reference is valid and seems to work in all other ways, the sheet references
seems to confuse Excel when you sort formulas with these unnecessary sheet
references.

If this is the cass, remove these extraneous sheet references, (only those
to the sheet the formula is on) and the sorting may work properly.

HTH,

TK
"Steve M" wrote:

> I believe I have tried your suggestion. Here is more of what I have.
>
> Formula initially in row 5 column B..
>
> = Index(F:F, Match( C5, G:G, 0))
>
> after a sort row 5 is now row 10 but the formula remains
>
> = Index(F:F, Match( C5, G:G, 0))
>
> instead of
>
> = Index(F:F, Match( C10, G:G,0))
>
> "T Kirtley" wrote:
>
> > Try referencing entire columns in your index() and match() functions, using
> > syntax like B:B instead of specific rows like B1:B48
> >
> > HTH,
> >
> > TK
> >
> > "Steve M" wrote:
> >
> > > I have a worksheet where I use the combination of the index & Match function
> > > to retrieve data from other sheets in a workbook. The retrieved data is in
> > > multiple rows and mltiple columns. Each column has autofilter enabled so I
> > > can quickly sort for the rows with specific data. With Office 2003 the
> > > autofilter also allows for quick sortation of the data. My problem is that
> > > within the index match formula the match lookup value does not update row
> > > number as the filter sorting is applied. In other words, if the original
> > > match formula was referencing a lookup value in row 48 and that row changed
> > > as a result of the sort to row 100 the formula still is referencing row 48
> > > instead of the new row 100.
> > >
> > > Does anyone know of a work arround? Is there a "trick" I am unaware of?
> > > Thanks for any assistance.

Thanks for the help. There was a sheet reference for the lookup_value which
was valid but unecessary. I removed it as you suggested and it worked.
Thanks again.


"T Kirtley" wrote:

> By chance, is there a sheet reference in front of the "Lookup_Value" of the
> Match() formula? (I am presuming that this is on the same page as your
> Index(Match)) formulas).
>
> If you create a formula by paging back and forth among the worksheets in the
> workbook, Excel will put a sheet reference in for each cell reference of the
> formula you create... even references to the sheet the formula is on. Thus a
> formula would look like =INDEX(Data!B:B,MATCH(Sheet1!A1,Data!A:A,0)) instead
> of =INDEX(Data!B:B,MATCH(A1,Data!A:A,0)). Although the unnecessary sheet
> reference is valid and seems to work in all other ways, the sheet references
> seems to confuse Excel when you sort formulas with these unnecessary sheet
> references.
>
> If this is the cass, remove these extraneous sheet references, (only those
> to the sheet the formula is on) and the sorting may work properly.
>
> HTH,
>
> TK
> "Steve M" wrote:
>
> > I believe I have tried your suggestion. Here is more of what I have.
> >
> > Formula initially in row 5 column B..
> >
> > = Index(F:F, Match( C5, G:G, 0))
> >
> > after a sort row 5 is now row 10 but the formula remains
> >
> > = Index(F:F, Match( C5, G:G, 0))
> >
> > instead of
> >
> > = Index(F:F, Match( C10, G:G,0))
> >
> > "T Kirtley" wrote:
> >
> > > Try referencing entire columns in your index() and match() functions, using
> > > syntax like B:B instead of specific rows like B1:B48
> > >
> > > HTH,
> > >
> > > TK
> > >
> > > "Steve M" wrote:
> > >
> > > > I have a worksheet where I use the combination of the index & Match function
> > > > to retrieve data from other sheets in a workbook. The retrieved data is in
> > > > multiple rows and mltiple columns. Each column has autofilter enabled so I
> > > > can quickly sort for the rows with specific data. With Office 2003 the
> > > > autofilter also allows for quick sortation of the data. My problem is that
> > > > within the index match formula the match lookup value does not update row
> > > > number as the filter sorting is applied. In other words, if the original
> > > > match formula was referencing a lookup value in row 48 and that row changed
> > > > as a result of the sort to row 100 the formula still is referencing row 48
> > > > instead of the new row 100.
> > > >
> > > > Does anyone know of a work arround? Is there a "trick" I am unaware of?
> > > > Thanks for any assistance.

Man thank you! Unnecessary references were messing up mine too! Thanks again!

This was a great help, wouldn't have thought to remove the sheet reference, definitely saved me a lot of time trying to figure this out myself.