Use 3 criteria to find piece of data

[jonathan92591]

Excel Workbook
	A	B	C	D	E
1	Hello everyone,	*	*	*	*
2	*	*	*	*	*
3	Use 3 criteria to find piece of data	*	*	*	*
4	*	*	*	*	*
5	--	*	*	*	*
6	*	*	*	*	*
7	Excel or Access version:	Excel 2010	*	*	*
8	*	*	*	*	*
9	Computer operating system:	Windows XP	*	*	*
10	*	*	*	*	*
11	Sample data:	EmployeID	Month	TSBM 1-15	TSBM 16-31
12	*	JCB001	Jan	50000	80000
13	*	JCB001	Feb	55000	85000
14	*	JCB001	Mar	60000	90000
15	*	JCB001	Apr	65000	95000
16	*	JCB001	May	70000	100000
17	*	JCB001	Jun	75000	105000
18	*	JCB001	Jul	80000	110000
19	*	JCB001	Aug	85000	115000
20	*	JCB001	Sep	90000	120000
21	*	JCB001	Oct	95000	125000
22	*	JCB001	Nov	100000	130000
23	*	JCB001	Dec	105000	135000
24	Formula(s) right now:	= VLOOKUP(K4,BE!$1:$1048576,MATCH('JCB001'!$B$5,BE!$1:$1,0),0)	*	*	*
25	*	*	*	*	*
26	Current result(s):	50000	*	*	*
27	*	*	*	*	*
28	My goal:	To have the formula look at the employee, the time period, and the month, and return a number from the backend data sheet.	*	*	*
29	*	*	*	*	*
30	Error message:	No error message	*	*	*
31	*	*	*	*	*
32	How error occurred:	No error message	*	*	*
33	*	*	*	*	*
34	Generated in:	Excel 2010	*	*	*
35	*	*	*	*	*
36	Thank you.	*	*	*	*
37	*	*	*	*	*
38	JT :)	*	*	*	*
Form

Here is the dashboard I'm working on.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
4	*	DAYS		*	MO vs. PERSONAL GOAL		HIGH SALES		YTD MO. AVG	*	JCB001
5	*	TSBM 1-15	TSBM 16-31	TOTAL MO	MS Goal	%	$	PC	$ * * * * *4,167	*	YEAR	CAREER SALES
6	Jan	$50,000	$0	$50,000	#N/A	0%	$115,790	DRM	1100%	*	2006	#N/A
7	Feb	$0	$0	$0	#N/A	0%	$113,006	SGF	-100%	*	2007	#N/A
8	Mar	$0	$0	$0	#N/A	0%	$115,935	DRM	-100%	*	2008	#N/A
9	Apr	$0	$0	$0	#N/A	0%	$109,580	JCB	-100%	*	2009	#N/A
10	May	$0	$0	$0	#N/A	0%	$121,403	DRM	-100%	*	2010	#N/A
11	Jun	$0	$0	$0	#N/A	0%	$120,115	JCB	-100%	*	2011	#N/A
12	Jul	$0	$0	$0	#N/A	0%	$172,353	SGF	-100%	*	Total	#N/A
13	Aug	$0	$0	$0	#N/A	0%	$110,191	JCB	-100%	*	*	*
14	Sep	$0	$0	$0	#N/A	0%	$110,765	SGF	-100%	*	*	*
15	Oct	$0	$0	$0	#N/A	0%	$129,054	TMI	-100%	*	*	*
16	Nov	$0	$0	$0	#N/A	0%	$100,354	SGF	-100%	*	*	*
17	Dec	$0	$0	$0	#N/A	0%	$129,670	TMI	-100%	*	*	*
18	*	$50,000	$0	$50,000	#N/A	0%	$1,448,216	*	*	*	*	*
JCB001

 

[jonathan92591]

I've been working on sumproduct.

Here is my current formula.

=SUMPRODUCT(--(BE!$A$2:$A$1000='JCB001'!K4:L4),--(BE!$B$2:$B$1000='JCB001'!$A7),(BE!$C$2:$C$1000))

Here is my result.

#VALUE!

My desired value returned is: 55000

 

[jonathan92591]

I found a solution.

Formula: =SUMPRODUCT(--(BE!$A$2:$A$1000='JCB001'!$K$4),--(BE!$B$2:$B$1000='JCB001'!$A7),BE!$C$2:$C$1000)

Returns: $55,000

Thanks for your help though if you were trying! Hopefully this helps someone else.

My quick recap: I was trying to use vlookup and match and something else, when really I just needed a sumproduct. Sumproduct is neat, just make sure you have the same formatting for your cells, understand the "double negative" aspect, and don't include headings in your ranges.

Thanks and good day to you all!

JT