Solved

# Index Matching

Posted on 2011-03-07
Medium Priority
229 Views
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
Question by:vegas86
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 10

Expert Comment

ID: 35063661
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

LVL 10

Expert Comment

ID: 35063717
For example you could do with Indirects and sumif's SchoolExample-1-.xlsx
0

Author Comment

ID: 35063815
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

LVL 24

Accepted Solution

jimyX earned 1000 total points
ID: 35063894
SchoolSampleUpdated.xls
0

LVL 10

Assisted Solution

Makrini earned 1000 total points
ID: 35063901
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

Author Closing Comment

ID: 35064062
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month9 days, 15 hours left to enroll