Sticking with the same example as above, let’s use MATCH to figure out what row Kevin is in. Type the column number for Height, which is “2” and close the bracket.Type the row number for Kevin, which is “4” and add a comma.
HOW TO CONSOLIDATE DATA IN EXCEL FROM MULTIPLE COLUMNS HOW TO
We want to use the INDEX formula to look up Kevin’s height… here is an example of how to do it. contacts.csv!$A:$A = Locked range that contains the list of values you're cross-referencing against (such as the "Client Name" or "Matter Number" column)īelow is a table showing people’s name, height and weight.the Client's Name or Matter Number) that you're using to cross-reference. matters.csv! F2 = Unlocked single Cell in the current report that contains the lookup data (eg.contacts.csv!$H:$H = Locked range that contains the values you want to pull in.The basic formula structure goes like this: =INDEX( contacts.csv!$H:$H,MATCH( matters.csv! F2, contacts.csv!$A:$A,0)) This is especially useful in Clio, as it allows you to bring in data from other reports, so long as there is data in common on both reports (such as a "Name", "Matter Number", or "ID" field, allowing you to cross-reference.) For short, this is referred to as just the Index Match function. =MATCH() returns the position of a cell in a row or column.Ĭombined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria.=INDEX() returns the value of a cell in a table based on the column and row number.The INDEX MATCH formula is the combination of two functions in Excel: INDEX and MATCH.
Using INDEX / MATCH to create custom reports.Cross-reference data from multiple reports using Excel.