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.

Index, Match and filters
 

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.

Index, Match and filters
 

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.

Index, Match and filters
 

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.

Index, Match and filters
 

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.