Forum Discussion
Issue finding the proper formula or macro
Hi Roland,
You could do this with 'index match'
There is a tutorial here that explains how it works:
https://www.deskbright.com/excel/using-index-match/
Although if you google 'index match' then loads of tutorials will come up.
I've attached an example of it working, I've put everything on the one worksheet to make it easier to see how it working.
While Index and Match are great if you have complex data, I would use VLOOKUP with the way you have yours set up. I have also come to appreciate the use of tables for data, rather than just placing the data in the worksheet. Tables make formula reference a lot easier. In the attached document, I placed the information you gave us on sheet 1 or "Rollup" as I've called it, and in a table that I've named "RollupTbl". There are separate worksheets for Class A Results, etc, and the data on each sheet is stored in a table as well, ClassATbl, etc. I use the Iferror first in case the name doesn't have a match (blank, as in my example, or different spelling, etc.), and the If statement next to handle blank fields (without it, the formula was returning 0 for blanks, which is 1/1/1900 if the cell is formatted as a date). To add a new line to the table, just put your cursor in the bottom right cell and hit tab
You should be able to sort your tables without it moving the formulas around.. I think Bob's point about using tables is really good. If you have time check out this:
and/or google something like 'excel tables'.
There are loads of advantages to table, but being able to refer to them 'by name' rather than as cell references is one that will defiantly help you here.
- Thanks!! This is very helpful
