Lookup a column name for a reference in a cell

[PitMyShants]

Hi all. First time poster here. I have found myself in an odd spot and am wondering if it is possible to do something instead of using a pivot table for said purpose. I'd prefer not to use a pivot table as the data will change often and I am likely to gain/lose values. I do not want to worry about my pivot resizing because I wish to compare the values from the pivot with another sheet. I know, odd situation but I'm really just curious.

Take a look at my pic here. In Sheet 2, I want to use a COUNTIFS function. The piece that I am missing is labeled XXX in the formula within column J. Ideally, I'd be able to substitute another formula for XXX that would return column D as a match (based on the value in H3. Is there a formula that I could use here with the array A2:F2 in Sheet 1 to return the value "Vendor1" which could then be used in the COUNTIFS function?

The big reason for this is because In this case there are 20 different columns I'd expect to use as an array in the COUNTIFS function. Certainly not the end of the world if I had to hardcode what cols I had for each one, but again.. curious.

 

[bebo021999]

Book1
	A	B	C	D	E	F
2	ID	Type	Price	Vendor1	Vendor2	Vendor3
3	1	DRZ	101	UNDER	OVER	OVER
4	2	DRZ	102	OVER	OVER	OVER
5	3	SXC	101	OVER	UNDER	OVER
6	4	SXC	104	UNDER	UNDER	OVER
7	5	LTZ	103	UNDER	UNDER	OVER
8	6	DRZ	100	UNDER	UNDER
Sheet1

Book1
	A	B	C	D
2	Vendor	Type	Under	Over
3	Vendor1	DRZ	2	1
4	Vendor1	SX	0	0
5	Vendor2	DRZ	1	2
Sheet2

Cell Formulas
Range	Formula
C3:D5	C3	=SUMPRODUCT((Sheet1!$B$3:$B$8=$B3)*(Sheet1!$D$2:$F$2=$A3)*(Sheet1!$D$3:$F$8=C$2))