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.
Another question expanding on this. I would like to be able to sort the columns and maintain the data in the column....if this is possible. While this is working successfully, I noticed that after the formula is in the cell and if I sort a column, the data changes because the formula has a specific Excel cell built into it. As you can see below, the formula is using cell B16 as the source of the MATCH. When I sort a column, the formula does not change and it is looking for whatever data that is in cell B16 after the column was sorted.
This is the actual formula that I am successfully using in Excel from your guidance =INDEX(EVULN405!I:I,MATCH(FLD_Employees!B16,EVULN405!B:B,0))
Cells in column B contain a person's unique employee ID# and column A contains that person's name.
Can this INDEX MATCH formula be altered to replace B16 (which would also affect the B:B in the formula) with an identifier that will stay with the person if the column is sorted?
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
- Bob this is great advice. Thanks!
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