Elementary Index Match Issue

[morerockin]

I've been away from Excel for a few months and cannot make my Index match work! So frustrating.

See attached.

In the table in Row 19, I'm trying to get it to do the following:

• Cell B22 should match the week number in B21 with the week number in B4, match the Market in A22 with the markets in columin A4:A13, and return the value listed in B5.
• Cell C22 should match the week number in B21 with the week number in J4, match the Market in A22 with the markets in column I4:I8 and return the value listed in J5.

See attached

[Zer0Cool]

• Cell A22 should match the week number in B21 with the week number in J4, match the Market in A22 with the markets in column I4:I8 and return the value listed in J5.

I am guessing you mean C22. It looks like you have not set your lookup arrays to absolute ranges, meaning as you fill them down A4:A13 and I4:I8 adjust relative to the position of your formula. Set them absolute and that should start to address your issue if not fix it.

EDIT: In addition it looks like your formulas have some redundancy in them.

Please Login or Register  to view this content.

Not sure I see the purpose for the concatenate in the above. Your index is referencing a single column, and the MATCH for your INDEX row only needs to match A22 to A4:A12. If you want to also match the week then you need to use match for the column arg too and have it look across the headers whilst having INDEX's range be something like B5:F12.

The other formula is basically the same issue.

[Richard Buttrey]

Hi

B22 & D22 copied down

Please Login or Register  to view this content.

Similar for the Actuals.


However if this is the start of any serious analysis I'd advose you to rethink your layout. Personally I'd create a 2 dimensional database with column fields for

1. Date
2. Market
3. Forecast / Actual
4. Value

Then use a Pivot Table to summarise your data by week - use the Grouping option in the Pivot Table. Not only will this be a lot more flexible but it's also easier to maintain and add to.

[morerockin]

Hi

B22 & D22 copied down

Please Login or Register  to view this content.

Similar for the Actuals.


However if this is the start of any serious analysis I'd advose you to rethink your layout. Personally I'd create a 2 dimensional database with column fields for

1. Date
2. Market
3. Forecast / Actual
4. Value

Then use a Pivot Table to summarise your data by week - use the Grouping option in the Pivot Table. Not only will this be a lot more flexible but it's also easier to maintain and add to.

I misguided you on accident because of bad instructions. Pleas see revised:

•Cell C22 should match the week number in B21 with the week number in J4, match the Market in A22 with the markets in column I4:I8 and return the value listed in J5

[morerockin]

I try this and it doesn't work:

=INDEX($B$5:$F$13,MATCH(A22&B21,A4:A13&B4:B13,0))

[Richard Buttrey]

I misguided you on accident because of bad instructions. Pleas see revised:

•Cell C22 should match the week number in B21 with the week number in J4, match the Market in A22 with the markets in column I4:I8 and return the value listed in J5

That's why I said "Similar for the Actuals."

I intended you to note and understand how the B22 formula worked and apply it with modifications to the C22 cell. i.e. for your own long term benefit I'm supplying the fishing rod not the fish.

[morerockin]

That's why I said "Similar for the Actuals."

I intended you to note and understand how the B22 formula worked and apply it with modifications to the C22 cell. i.e. for your own long term benefit I'm supplying the fishing rod not the fish.

Worked!! Thanks!!