• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Index Matching

Hello,

Can someone please have a look at the attached workbook and advise me on the correct index match formula that I would need in order to match the specific school and account code to the financial year total?
I have attached an example of what I am trying to describe. Basically the cells in red I would like to have index match formulas instead of Vlookups. From there I would like to index match the school and account code to the financial year.

Any advice would be appreciated at this point in time

SchoolExample.xlsx
0
vegas86
Asked:
vegas86
  • 3
  • 2
2 Solutions
 
MakriniCommented:
Do the financial years have to be that exact format?

It would be better to create a "helper" column in column A if that were possible, would make a far quicker set of lookups.  

Also VLookup is probably more suitable than index matches - although can index match if you wish
0
 
MakriniCommented:
For example you could do with Indirects and sumif's SchoolExample-1-.xlsx
0
 
vegas86Author Commented:
I would prefer index matching if possible but if not my main objective is to basically have the same formula through out the sheet so there isn't so much room for error when others use the workbook
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jimyXCommented:
How about this approach?
SchoolSampleUpdated.xls
0
 
MakriniCommented:
The solution I provided means you don't have to modify the sheet name in the formula.  

As far as I know, indirect is the only way to do this..

Index match for A4

=INDEX(Lup!D:D,MATCH(B4,Lup!C:C,FALSE))

Index Match for D4
=INDEX(Lup!B:B,MATCH(B4,Lup!A:A,FALSE))
0
 
vegas86Author Commented:
Thanks guys, both answers were very helpful in their own ways!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now