Match two row headings, plus a column heading, and return intersection

[Cowzilla]

Hi There,

I have two worksheets..one is a bunch of data, and the other is a summary sheet. I need to pull data into the summary sheet by having it match two different row headings, a column heading, and then return the value of the corresponding intersection. I can't modify the data or headings in anyway, only the summary sheet formulas.

If someone can tell me how to upload examples or screenshots, I gladly will.

Thanks!!

 

[T. Valko]

Hi There,

I have two worksheets..one is a bunch of data, and the other is a summary sheet. I need to pull data into the summary sheet by having it match two different row headings, a column heading, and then return the value of the corresponding intersection. I can't modify the data or headings in anyway, only the summary sheet formulas.

If someone can tell me how to upload examples or screenshots, I gladly will.

Thanks!!

See if this is what you had in mind.

Book1
	A	B	C	D	E	F
1	_	_	A	B	C	D
2	Z	Z	88	33	54	5
3	X	Y	68	88	91	44
4	S	D	95	3	19	70
5	H	J	54	37	62	69
Sheet1

Book1
	H	I	J	K
1	Lookup Values			Result
2	X	Y	B	88
Sheet1

This array formula** entered in K2:

=INDEX(C2:F5,MATCH(1,IF(A2:A5=H2,IF(B2:B5=I2,1)),0),MATCH(J2,C1:F1,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Cowzilla]

The formula doesn't seem to be pulling the data from the correct line unless I index it just to the line where the data is. It's also giving me a REF error when I try to copy it to other cells. Here is my exact formula:

{=INDEX('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$90:$AM$90,MATCH(1,IF('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$3:$AM$3=$B5,IF('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$4:$AM$4=$A5,1)),0),MATCH("OVERALL TOTAL",'[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$A$90,0))}

The bold areas are where I would like it to search a larger area, rather than me having to absolute reference just the cells with the data I'm looking for.

 

[taurean]

To upload screen shots like above, you will need html maker Add-In. Here is one link which I used and am using since yesterday:
http://www.excel-jeanie-html.de/index.php?f=1

 

[T. Valko]

The formula doesn't seem to be pulling the data from the correct line unless I index it just to the line where the data is. It's also giving me a REF error when I try to copy it to other cells. Here is my exact formula:

{=INDEX('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$90:$AM$90,MATCH(1,IF('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$3:$AM$3=$B5,IF('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$4:$AM$4=$A5,1)),0),MATCH("OVERALL TOTAL",'[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$A$90,0))}

The bold areas are where I would like it to search a larger area, rather than me having to absolute reference just the cells with the data I'm looking for.

Ok...

You're only indexing a single row of data: $I$90:$AM$90

It looks like you're searching 2 rows (not columns) for multiple lookup values so that portion needs to be moved here:

=INDEX('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$90:$AM$90,MATCH("OVERALL TOTAL",'[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$A$90,0),MATCH(1,IF('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$3:$AM$3=$B5,IF('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$4:$AM$4=$A5,1)),0))

So, you need to complete the black bolded range and then expand the red bolded range.

 

[T. Valko]

Ok...

You're only indexing a single row of data: $I$90:$AM$90

It looks like you're searching 2 rows (not columns) for multiple lookup values so that portion needs to be moved here:

=INDEX('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$90:$AM$90,MATCH("OVERALL TOTAL",'[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$A$90,0),MATCH(1,IF('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$3:$AM$3=$B5,IF('[JOB SCHEDULE PLOVER 2011.xls]ChipSeal 2011'!$I$4:$AM$4=$A5,1)),0))

So, you need to complete the black bolded range and then expand the red bolded range.

Book1
	A	B	C	D	E
1	_	Z	X	S	H
2	_	Z	Y	D	J
3	A	88	33	54	5
4	B	68	88	91	44
5	C	95	3	19	70
6	D	54	37	62	69
Sheet1

Book1
	H	I	J	K
1	Lookup Values			Result
2	X	Y	B	88
Sheet1

This array formula** entered in K2:

=INDEX(B3:E6,MATCH(J2,A3:A6,0),MATCH(1,IF(B1:E1=H2,IF(B2:E2=I2,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.