HLOOKUP from two fields

[canadian86]

Hi all,

I need a LOOKUP formula that looks up two fields and returns a result. (I cannot use a Pivot table there's a good reason for that, which is hard to explain.)

Here's the source file: Source File

I need to take data from the Raw Data worksheet and put it into the table in Sheet1. I've done one example, but as you can see -- I have to do a HLOOKUP for every single store. I want the formula to take into account the store name and return the right result.

Is that possible?

 

[ChrisM]

If you have Excel 2007+, use the SUMIFS formula.

If not, use a SUMPRODUCT formula. Do a web search on it, it's great for multi-criteria lookups.

 

[snoopyhr]

Try this

B15 is where data validation resides

And do not forget to confirm the formula in B18 ( to be copied cross) is an array and need to be confirmed with Ctrl Shift Enter

Excel Workbook
	A	B	C	D	E	F	G
1	Cable Name	# of Cable 50	# of Cable 100	# of Cable 150	# of Cable 50	# of Cable 100	# of Cable 150
2	Cable Type	Eastern	Eastern	Eastern	Western	Western	Western
3	Store 1	7	4	1	0	0	0
4	Store 2	6	4	3	0	0	0
5	Store 3	0	0	0	4	3	2
6	Store 4	0	0	0	2	3	1
7	*	*	*	*	*	*	*
8	These are my assumption:	*	*	*	*	*	*
9	Required extract based on Store # and Geographical region to return quantity of cable grade.	*	*	*	*	*	*
10	*	*	*	*	*	*	*
11	Given this is so, then this the way I would aprroach	*	*	*	*	*	*
12	*	*	*	*	*	*	*
13	First Create a range for data validation to pick up stores,	*	*	*	*	*	*
14	*	*	*	*	*	*	*
15	Select Store--->	Store 4	*	*	*	*	*
16	*	# of Cable 50	# of Cable 100	# of Cable 150	# of Cable 50	# of Cable 100	# of Cable 150
17	*	Eastern	Eastern	Eastern	Western	Western	Western
18	*	0	0	0	2	3	1
Sheet1