Multiple Criteria Lookup

[jweese74]

Looking for help with some sort of lookup, I've been unable to figure it out although I'm sure it's possible. I've tried vlookup, match, index, and a combination of the three but can't seem to get what I want, working.

Following are examples, not necessarily the information, but more simplistic.

Sheet 1 contains vital statistics for an individual, added to and updated using an effective "Change Date", for example:

Change Date     NAME             Address                Telephone        Email 
01-01-2018      MUNTZ NELSON     299 Simpson St         113-281-3187     nmuntz@simpsons.com 
01-01-2018      SIMPSON HOMER    124 Simpson St         971-981-2440     homerj@simpsons.com 
01-01-2018      SZYSLAK MOE      300 Drunkards Ave      510-754-7145     moestavern@simpsons.com 
01-01-2018      VAN HOUTEN M     100 Simpson St         526-470-7164     sexyhouse@simpsons.com 
05-31-2018      SIMPSON HOMER    124 Simpson St         818-071-1862     homerj@simpsons.com 
08-02-2018      WIGGUM RALPH     200 Crooks Way         232-794-5521     chiefbeef@simpsons.com 
09-15-2018      SIMPSON HOMER    124 Simpson St         421-103-2847     hsimpsons@simpsons.com

Sheet 2 contains an ongoing log of transactions for an individual, but is updated with vital statistics from Sheet 1 that were relevant at the time of the log entry. In this example, the effective date, name, and invoice amount would be added, and the remaining columns would be provided via a lookup of some sort with the data that was relevant at the time of effective date, for example:

Effective Date  NAME             Address          Telephone        Email                   Invoice Amount 
01-01-2018      SIMPSON HOMER    124 Simpson St   971-981-2440     homerj@simpsons.com     50.00 
02-01-2018      SIMPSON HOMER    124 Simpson St   971-981-2440     homerj@simpsons.com     72.00 
03-05-2018      SIMPSON HOMER    124 Simpson St   971-981-2440     homerj@simpsons.com     71.00 
04-20-2018      SIMPSON HOMER    124 Simpson St   971-981-2440     homerj@simpsons.com     74.00 
06-01-2018      SIMPSON HOMER    124 Simpson St   818-071-1862     homerj@simpsons.com     67.00 
07-02-2018      SIMPSON HOMER    124 Simpson St   818-071-1862     homerj@simpsons.com     55.00 
07-15-2018      SIMPSON HOMER    124 Simpson St   818-071-1862     homerj@simpsons.com     66.00 
07-18-2018      SIMPSON HOMER    124 Simpson St   818-071-1862     homerj@simpsons.com     64.00 
11-20-2018      SIMPSON HOMER    124 Simpson St   421-103-2847     hsimpsons@simpsons.com  79.00

Thoughts around this?

 

[excel_learnerz]

just so I fully understand, are you looking for values in sheet 2 to be summarised in sheet 1 with the effective date in sheet 2 as the change date in sheet 1?

 

[jweese74]

just so I fully understand, are you looking for values in sheet 2 to be summarised in sheet 1 with the effective date in sheet 2 as the change date in sheet 1?

I am looking to draw the Address, Telephone, and Email from Sheet 1 into Sheet 2, using the Address, Telephone, and Email from Sheet 1 that was relevant at the time of the Effective Date in Sheet 1.

For example, Mr. Simpson has two changes, one on January 1st, one on March 31st. If I enter an invoice on February 1st, Sheet 2 will use the data for the period between January 1st and March 30th (Address, Telephone, and Email), if I enter an invoice on an effective date of April 5th, Sheet 2 will use the Address, Telephone, and Email for the period March 31st since the data provided on March 31st is the most current information at the time of invoice.

Does that help?

 

[AlanY]

deleted

 

[jweese74]

deleted

You've got it, almost, this looks quite promising, however, when I tested it and changed an effective date to (for example) 08-06-2018 I receive an #N/A error.

 

[AlanY]

try this

Book1
	A	B	C	D	E	F
1	Change Date	NAME	Address	Telephone	Email
2	01/01/2018	MUNTZ NELSON	299 Simpson St	113-281-3187	nmuntz@simpsons.com
3	01/01/2018	SIMPSON HOMER	124 Simpson St	971-981-2440	homerj@simpsons.com
4	01/01/2018	SZYSLAK MOE	300 Drunkards Ave	510-754-7145	moestavern@simpsons.com
5	01/01/2018	VAN HOUTEN M	100 Simpson St	526-470-7164	sexyhouse@simpsons.com
6	31/05/2018	SIMPSON HOMER	124 Simpson St	818-071-1862	homerj@simpsons.com
7	02/08/2018	WIGGUM RALPH	200 Crooks Way	232-794-5521	chiefbeef@simpsons.com
8	15/09/2018	SIMPSON HOMER	124 Simpson St	421-103-2847	hsimpsons@simpsons.com
9
10	Effective Date	NAME	Address	Telephone	Email	Invoice Amount
11	01/01/2018	SIMPSON HOMER	124 Simpson St	971-981-2440	homerj@simpsons.com	50
12	01/02/2018	SIMPSON HOMER	124 Simpson St	971-981-2440	homerj@simpsons.com	72
13	05/03/2018	SIMPSON HOMER	124 Simpson St	971-981-2440	homerj@simpsons.com	71
14	20/04/2018	SIMPSON HOMER	124 Simpson St	971-981-2440	homerj@simpsons.com	74
15	01/06/2018	SIMPSON HOMER	124 Simpson St	818-071-1862	homerj@simpsons.com	67
16	02/07/2018	SIMPSON HOMER	124 Simpson St	818-071-1862	homerj@simpsons.com	55
17	15/07/2018	SIMPSON HOMER	124 Simpson St	818-071-1862	homerj@simpsons.com	66
18	18/08/2018	SIMPSON HOMER	124 Simpson St	818-071-1862	homerj@simpsons.com	64
19	20/11/2018	SIMPSON HOMER	124 Simpson St	421-103-2847	hsimpsons@simpsons.com	79
Sheet3

Cell Formulas
Range	Formula
C11	{=INDEX($C$2:$E$8,MATCH(LOOKUP($A11,IF($B$2:$B$8=$B11,$A$2:$A$8))&$B11,$A$2:$A$8&$B$2:$B$8,0),MATCH(C$10,$C$1:$E$1,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jweese74]

Perfect, this seems to do the trick - I've never used the curly braces before, great learning experience. Thanks again!

 

[AlanY]

you're welcome
glad that work for you.