Index and Match for date range

[Keegan4123]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Sheet 1 in file						Sheet 2 in file
2	Data Entry Sheet						Joes Rate Sheet				Aprils Rate Sheet
3		Client	Type	Rate	Rate should be		Current Till Date	Type	Rate		Current Till Date	Type	Rate
4	01-Jan-22	Joe	Apple	Index/Match Formula	$ 0.75		30-Apr-22	Apple	$ 0.75		30-Apr-22	Apple	$ 1.00
5	01-Jan-22	April	Orange	Index/Match Formula	$ 2.00		30-Apr-22	Orange	$ 1.75		30-Apr-22	Orange	$ 2.00
6	01-Jan-22	Joe	Grape	Index/Match Formula	$ 2.75		30-Apr-22	Grape	$ 2.75		30-Apr-22	Grape	$ 3.00
7	01-Jan-22	April	Mango	Index/Match Formula	$ 4.00		30-Apr-22	Mango	$ 3.75		30-Apr-22	Mango	$ 4.00
8	01-Jan-22	Joe	Pineapple	Index/Match Formula	$ 4.75		30-Apr-22	Pineapple	$ 4.75		30-Apr-22	Pineapple	$ 5.00
9	01-Mar-22	April	Apple	Index/Match Formula	$ 1.50		01-Nov-22	Apple	$ 1.25		01-Nov-22	Apple	$ 1.50
10	01-Mar-22	Joe	Orange	Index/Match Formula	$ 2.25		01-Nov-22	Orange	$ 2.25		01-Nov-22	Orange	$ 2.50
11	01-Mar-22	April	Grape	Index/Match Formula	$ 3.50		01-Nov-22	Grape	$ 3.25		01-Nov-22	Grape	$ 3.50
12	01-Mar-22	Joe	Mango	Index/Match Formula	$ 4.25		01-Nov-22	Mango	$ 4.25		01-Nov-22	Mango	$ 4.50
13	01-Mar-22	April	Pineapple	Index/Match Formula	$ 5.50		01-Nov-22	Pineapple	$ 5.25		01-Nov-22	Pineapple	$ 5.50
14	02-Nov-22	Joe	Apple	Index/Match Formula	$ 1.50		today()	Apple	$ 1.50		today()	Apple	$ 1.75
15	02-Nov-22	April	Orange	Index/Match Formula	$ 2.75		today()	Orange	$ 2.50		today()	Orange	$ 2.75
16	02-Nov-22	Joe	Grape	Index/Match Formula	$ 3.50		today()	Grape	$ 3.50		today()	Grape	$ 3.75
17	02-Nov-22	April	Mango	Index/Match Formula	$ 4.75		today()	Mango	$ 4.50		today()	Mango	$ 4.75
18	02-Nov-22	Joe	Pineapple	Index/Match Formula	$ 5.50		today()	Pineapple	$ 5.50		today()	Pineapple	$ 5.75
19
20
21	Date will be manual entered, Type will be drop down, rate will be the lookup from the rate sheet						Would like to have the rate sheets saved as named dynamic tables for easy referencing and reduce risk of breaking formulas as things grow
22
23
24
Sheet1

 

[mmhill]

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]

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]

@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]

@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.