# Index and Match for date range



## Keegan4123 (Dec 12, 2022)

I have a data entry sheet where we input daily sales based on type of product sold. I want to cross reference to have the client, and the date the product was sold to return a price on the product. The clients change on a line by line basis but remain constant and match with individual client rate sheets. As rates increase we continue the client rate sheet with new dates and new rates. 

Index Match Example.xlsxABCDEFGHIJKLM1Sheet 1 in fileSheet 2 in file2Data Entry SheetJoes Rate SheetAprils Rate Sheet3ClientTypeRateRate should beCurrent Till DateTypeRateCurrent Till DateTypeRate401-Jan-22JoeAppleIndex/Match Formula$                   0.7530-Apr-22Apple$       0.7530-Apr-22Apple$       1.00501-Jan-22AprilOrangeIndex/Match Formula$                   2.0030-Apr-22Orange$       1.7530-Apr-22Orange$       2.00601-Jan-22JoeGrapeIndex/Match Formula$                   2.7530-Apr-22Grape$       2.7530-Apr-22Grape$       3.00701-Jan-22AprilMangoIndex/Match Formula$                   4.0030-Apr-22Mango$       3.7530-Apr-22Mango$       4.00801-Jan-22JoePineappleIndex/Match Formula$                   4.7530-Apr-22Pineapple$       4.7530-Apr-22Pineapple$       5.00901-Mar-22AprilAppleIndex/Match Formula$                   1.5001-Nov-22Apple$       1.2501-Nov-22Apple$       1.501001-Mar-22JoeOrangeIndex/Match Formula$                   2.2501-Nov-22Orange$       2.2501-Nov-22Orange$       2.501101-Mar-22AprilGrapeIndex/Match Formula$                   3.5001-Nov-22Grape$       3.2501-Nov-22Grape$       3.501201-Mar-22JoeMangoIndex/Match Formula$                   4.2501-Nov-22Mango$       4.2501-Nov-22Mango$       4.501301-Mar-22AprilPineappleIndex/Match Formula$                   5.5001-Nov-22Pineapple$       5.2501-Nov-22Pineapple$       5.501402-Nov-22JoeAppleIndex/Match Formula$                   1.50today()Apple$       1.50today()Apple$       1.751502-Nov-22AprilOrangeIndex/Match Formula$                   2.75today()Orange$       2.50today()Orange$       2.751602-Nov-22JoeGrapeIndex/Match Formula$                   3.50today()Grape$       3.50today()Grape$       3.751702-Nov-22AprilMangoIndex/Match Formula$                   4.75today()Mango$       4.50today()Mango$       4.751802-Nov-22JoePineappleIndex/Match Formula$                   5.50today()Pineapple$       5.50today()Pineapple$       5.75192021Date will be manual entered, Type will be drop down, rate will be the lookup from the rate sheetWould like to have the rate sheets saved as named dynamic tables for easy referencing and reduce risk of breaking formulas as things grow222324Sheet1


----------



## mmhill (Dec 12, 2022)

Using tables makes this easy and flexible.  You just need 3 quick set-up steps. 

First, select the client tables and give them names ... I used \\[client name].  e.g., \\Joe, \\April, \\Ed  (You don't need the \\ but I use them to distinguish tables from ranges.)
Then, name the results table.  Doesn't matter what you name it. 
Finally, add a named range for the *Current Till Data* and *Type* columns in one of the customer tables - put the name in square brackets. You just need to do this for 1 table.

Name: \TillDate
Refersto: ="["&\\JOE[[#Headers],[Current Till Date]]&"]"

Name: \Type  
Refersto: ="["&\\JOE[[#Headers],[Type]]&"]"

Setup is done.  The rate formula is now ... 

=INDEX(INDIRECT("\\"&[@Client]),MATCH(1,([@Date]=INDIRECT("\\"&[@Client]&\TillDate))*([@Type]=INDIRECT("\\"&[@Client]&\Type)),0),3)

NOTE: This is an array formula, so do a CTRL + SHIFT + ENTER.  Then copy the formula down. 

FWIW, you can add as many criteria as you want just put them into the MATCH function: MATCH(1,(Value1=Range1)*(Value2=Range2)*(ValueN=RangeN),0)

This provides a lot of automatic flexibility ... every range will adjust as the Rates tables get more rows.  To add a new client, just copy an existing table, paste it where you want and rename the new table \\[client name].  You can also do this to move tables if you need to.  To change column headers, just type the new ones into the table headers.  The formula take care of the rest. 

A few caveats - no spaces permitted in table names, Rate table column headers cannot be formulas and need to be identical in every table.


----------



## Keegan4123 (Jan 4, 2023)

Hi @mmhill 

I think this should work, However running into a problem, not sure where but getting an error when I try use the formula that the syntax of the name isn't correct. I don't have any spaces, or anything in there not sure what is happening.


----------



## Keegan4123 (Jan 5, 2023)

@mmhill Disregard, got it figured out and it is working well so far in trials, have yet to put into practice on our actual working sheet, and a bit concerned with it being an array formula and if it might cause issues with calculation speed, but hopefully should be ok.


----------



## mmhill (Jan 5, 2023)

Keegan4123 said:


> @mmhill Disregard, got it figured out and it is working well so far in trials, have yet to put into practice on our actual working sheet, and a bit concerned with it being an array formula and if it might cause issues with calculation speed, but hopefully should be ok.



Didn't see you rmessage until just now. Good to hear.  I don't think you're going to see much speed issues unless you are dealign with very large tables.  If that is the case, you are much better off treating the source table as a data base and doing a simple SQL query on it.


----------

