Index and Match Function - Across Header and Down Rows

[jumpmanz5]

Hi All,

I'm hoping I can get a little help on indexing and matching. I've been working to try and sort this for about a week now and haven't been successful at all. I can get the information to pull in for the first company, but when i can the selection criteria i lose it.

I have two sheets in my excel file. Sheet 1 contains a list of labor categories (Column A), a cell to enter a company number (B2) (that name will then display in (B3). Sheet 2 contains that same listed of labor categories (Column A) and the in a row (row 1) a list of companies by name (Company A, B, C, etc). Each company has a number above it.

What I'm looking to have happen... when i enter a company number from Sheet 2 into Sheet 1 cell B2 that rows in Column B (B5:B14) are populated with the hourly rates from Sheet 2 for the corresponding labor category.

Example: If i enter a 1, in B2 on Sheet 1

On Sheet 1 in Column B all the labor rates from Sheet 2 will populate in that column. The first being $62.65, $72.92, $100.81 and so on down the column.

Any help is greatly appreciate. I think this is an array type formula, but i just cannot get anything to work.


<a href="http://sv.tinypic.com?ref=206fvns" target="_blank"><img src="http://i65.tinypic.com/206fvns.jpg" border="0" alt="Image and video hosting by TinyPic"></a>

<a href="http://sv.tinypic.com?ref=2ufzwvk" target="_blank"><img src="http://i67.tinypic.com/2ufzwvk.jpg" border="0" alt="Image and video hosting by TinyPic"></a>

 

[ChandraAmgain]

If i understood you, Try Below Formula: and next time please try to copy and paste example sheet instead of JPG.

=INDEX(Sheet2!$A$3:$D$10,MATCH(A7,Sheet2!$A$3:$A$10,0),MATCH($B$4,Sheet2!$A$3:$D$3,0))
[TABLE="width: 452"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]

[TABLE="width: 452"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sheet 1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Enter Subcontratr….>[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Company Name[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Labor Ceteogry[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]66[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 594"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Sheet 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Concatenated Name[/TD]
[TD]Company A[/TD]
[TD]Company B[/TD]
[TD]Company C[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]11[/TD]
[TD]77[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]22[/TD]
[TD]78[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]33[/TD]
[TD]79[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD]44[/TD]
[TD]80[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]55[/TD]
[TD]81[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]66[/TD]
[TD]82[/TD]
[TD]90

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Lobsterboy1]

You could also put a lookup in cell B3 to pull in the company name.

=HLOOKUP($B$2,Sheet2!$B$1:$D$2,2,0)

 

[jumpmanz5]

If i understood you, Try Below Formula: and next time please try to copy and paste example sheet instead of JPG.

=INDEX(Sheet2!$A$3:$D$10,MATCH(A7,Sheet2!$A$3:$A$10,0),MATCH($B$4,Sheet2!$A$3:$D$3,0))
[TABLE="width: 452"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]

[TABLE="width: 452"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sheet 1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Enter Subcontratr….>[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Company Name[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Labor Ceteogry[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]66[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 594"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Sheet 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Concatenated Name[/TD]
[TD]Company A[/TD]
[TD]Company B[/TD]
[TD]Company C[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]11[/TD]
[TD]77[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]22[/TD]
[TD]78[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]33[/TD]
[TD]79[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD]44[/TD]
[TD]80[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]55[/TD]
[TD]81[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]66[/TD]
[TD]82[/TD]
[TD]90

[/TD]
[/TR]
</tbody>[/TABLE]

Hi ChandraAmgain,

Really appreciate the suggested, but i was not able to get it to work (probably me). I installed the Mr.ExcelHtml maker, so pasting these two table i hope should make it easier to understanding what i'm actually tracking to do.

The first table is the main sheet that i want information (labor rates) pulled into from the second table. What i'm looking to do is input a Subcontractor # (C1 - Table 1) from the Subcontractor #s in second table (ROW 1 - Columns C, D, E (in black) .

When i insert the number it auto pulls the hourly rates for the company. So if i input 1, it would pull all the labor rates from Company A in the second table in column C to the corresponding labor categories (Column B in both tables) in the first table.

If i entered 2, it would pull labor rates for Company B and so on. My actual file contains over 95 company names and 289 labor categories for each company, so you can somewhat see why it would be nice to simply input 1 number and get all the rate for that company vice sorting for them.

Again, i appreciate the help. Thank You

Excel 2012
	A	B	C	D	E
1			1	<-Enter Subcontract # from SubktLaborRates Sheet
2	No.	Concatenated Name	Company A
3	1	Enterprise IT Administration Database Administrator Developmental
4	2	Enterprise IT Administration Database Administrator Full Performance
5	3	Enterprise IT Administration Database Administrator Senior
6	4	Enterprise IT Administration Database Administrator Expert
7	5	Enterprise IT Administration Database Administrator Subject Matter Expert
8	6	Enterprise IT Administration Database Administrator Manager
9	7	Enterprise IT Administration Systems Administrator Developmental
10	8	Enterprise IT Administration Systems Administrator Full Performance
11	9	Enterprise IT Administration Systems Administrator Senior
12	10	Enterprise IT Administration Systems Administrator Expert
CompetitiveRange

Excel 2012
	A	B	C	D	E
1			1	2	3
2	No.	Concatenated Name	Company A	Company B	Compnay C
3	1	Enterprise IT Administration Database Administrator Developmental	$62.65	$73.59	$74.94
4	2	Enterprise IT Administration Database Administrator Full Performance	$72.92	$90.28	$88.39
5	3	Enterprise IT Administration Database Administrator Senior	$100.81	$118.22	$105.68
6	4	Enterprise IT Administration Database Administrator Expert	$109.46	$131.36	$124.90
7	5	Enterprise IT Administration Database Administrator Subject Matter Expert	$130.78	$136.26	$151.80
8	6	Enterprise IT Administration Database Administrator Manager	$133.25	$138.03	$151.80
9	7	Enterprise IT Administration Systems Administrator Developmental	$67.79	$69.55	$73.34
10	8	Enterprise IT Administration Systems Administrator Full Performance	$74.57	$79.76	$83.58
11	9	Enterprise IT Administration Systems Administrator Senior	$89.40	$103.58	$97.99
12	10	Enterprise IT Administration Systems Administrator Expert	$108.51	$119.58	$121.06
SubktrLaborRates

 

[Lobsterboy1]

This should be what you have, if you put number 1,2 or 3 in cell C1 the company name and values should change to whatever you have in the SubktrLaborRates sheet.
CompetitiveRange

*	A	B	C	D
*	*	<-Enter Subcontract # from SubktLaborRates Sheet
No.	Concatenated Name	Company A	*
Enterprise IT Administration Database Administrator Developmental	$62.65	*
Enterprise IT Administration Database Administrator Full Performance	$72.92	*
Enterprise IT Administration Database Administrator Senior	$100.81	*
Enterprise IT Administration Database Administrator Expert	$109.46	*
Enterprise IT Administration Database Administrator Subject Matter Expert	$130.78	*
Enterprise IT Administration Database Administrator Manager	$133.25	*
Enterprise IT Administration Systems Administrator Developmental	$67.79	*
Enterprise IT Administration Systems Administrator Full Performance	$74.57	*
Enterprise IT Administration Systems Administrator Senior	$89.40	*
Enterprise IT Administration Systems Administrator Expert	$108.51	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:29px;"><col style="width:481px;"><col style="width:77px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]10[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =HLOOKUP($C$1,SubktrLaborRates!$C$1:$E$2,2,0) C3 =INDEX(SubktrLaborRates!$A$3:$E$12,MATCH(A3,SubktrLaborRates!$A$3:$A$12,0),MATCH($C$2,SubktrLaborRates!$A$2:$E$2,0)) C4 =INDEX(SubktrLaborRates!$A$3:$E$12,MATCH(A4,SubktrLaborRates!$A$3:$A$12,0),MATCH($C$2,SubktrLaborRates!$A$2:$E$2,0)) C5 =INDEX(SubktrLaborRates!$A$3:$E$12,MATCH(A5,SubktrLaborRates!$A$3:$A$12,0),MATCH($C$2,SubktrLaborRates!$A$2:$E$2,0)) C6 =INDEX(SubktrLaborRates!$A$3:$E$12,MATCH(A6,SubktrLaborRates!$A$3:$A$12,0),MATCH($C$2,SubktrLaborRates!$A$2:$E$2,0)) C7 =INDEX(SubktrLaborRates!$A$3:$E$12,MATCH(A7,SubktrLaborRates!$A$3:$A$12,0),MATCH($C$2,SubktrLaborRates!$A$2:$E$2,0)) C8 =INDEX(SubktrLaborRates!$A$3:$E$12,MATCH(A8,SubktrLaborRates!$A$3:$A$12,0),MATCH($C$2,SubktrLaborRates!$A$2:$E$2,0)) C9 =INDEX(SubktrLaborRates!$A$3:$E$12,MATCH(A9,SubktrLaborRates!$A$3:$A$12,0),MATCH($C$2,SubktrLaborRates!$A$2:$E$2,0)) C10 =INDEX(SubktrLaborRates!$A$3:$E$12,MATCH(A10,SubktrLaborRates!$A$3:$A$12,0),MATCH($C$2,SubktrLaborRates!$A$2:$E$2,0)) C11 =INDEX(SubktrLaborRates!$A$3:$E$12,MATCH(A11,SubktrLaborRates!$A$3:$A$12,0),MATCH($C$2,SubktrLaborRates!$A$2:$E$2,0)) C12 =INDEX(SubktrLaborRates!$A$3:$E$12,MATCH(A12,SubktrLaborRates!$A$3:$A$12,0),MATCH($C$2,SubktrLaborRates!$A$2:$E$2,0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


SubktrLaborRates

*	A	B	C	D	E
*	*
No.	Concatenated Name	Company A	Company B	Compnay C
Enterprise IT Administration Database Administrator Developmental	$62.65	$73.59	$74.94
Enterprise IT Administration Database Administrator Full Performance	$72.92	$90.28	$88.39
Enterprise IT Administration Database Administrator Senior	$100.81	$118.22	$105.68
Enterprise IT Administration Database Administrator Expert	$109.46	$131.36	$124.90
Enterprise IT Administration Database Administrator Subject Matter Expert	$130.78	$136.26	$151.80
Enterprise IT Administration Database Administrator Manager	$133.25	$138.03	$151.80
Enterprise IT Administration Systems Administrator Developmental	$67.79	$69.55	$73.34
Enterprise IT Administration Systems Administrator Full Performance	$74.57	$79.76	$83.58
Enterprise IT Administration Systems Administrator Senior	$89.40	$103.58	$97.99
Enterprise IT Administration Systems Administrator Expert	$108.51	$119.58	$121.06

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:481px;"><col style="width:77px;"><col style="width:76px;"><col style="width:76px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]10[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[jumpmanz5]

THAT'S IT!! Thank you...

Wasn't difficult to expand this to cover out to Column CY and Row 296 in my actual file.

Thanks again!!

 

[ChandraAmgain]

May be my explaination was week

=INDEX(SubktrLaborRates!$A$1:$E$12,MATCH(CompetitiveRange!B3,SubktrLaborRates!$B$1:$B$12,0),MATCH(CompetitiveRange!$C$1,SubktrLaborRates!$A$1:$E$1,0))