Index and Match Formula

[Chris Waller]

I have a workbook that contains about 6 different spreadsheets. One of the sheets is supposed to be a summary of all the others. The only thing in common on all the spreadsheets is a unique identifier which is in column C. From what I have read it appears that I need to use index and match type formula, but I am not having much look and have nearly pulled out the remainder of my hair.</SPAN>

On sheet one in column B there is a formula picking up the unique identifier from column B on sheet 2. What I now need to do is lift the names associated with the unique identifier which are in column A on sheet 2. Then there are several columns on sheet B after the unique identifier which also need to be displayed on sheet 1.</SPAN>

The subsequent sheets also contain the same unique identifier and I need all the data that is to the right of this identifier.</SPAN>

I have spent most of the day looking at this and not making any progress.</SPAN>

I can tell you that the data on the spreadsheets is NOT of a uniform length, and the version of Excel I am using at work is 2002. TIA</SPAN>

 

[konew1]

Your question isn't clear to me but I think this will help. I typed data into sheet 2, and extract it to sheet1. with the unique identifier in different order.

Excel Workbook
	A	B	C	D	E	F
1	Name		UniqueID	Type	MoreData	MoreDataB
2	Joe		12	m	MD1	MDb1
3	Bob		14	m	MD2	MDb2
4	Tane		34	m	MD3	MDb3
5	Jane		56	f	MD4	MDb4
Sheet2

Excel Workbook
	A	B	C	D	E
1	name	UniqueID	Type	MoreData	MoreDataB
2	Joe	12	m	MD1	MDb1
3	Jane	56	f	MD4	MDb4
4	Tane	34	m	MD3	MDb3
5	Bob	14	m	MD2	MDb2
Sheet1

 

[Chris Waller]

Your question isn't clear to me but I think this will help. I typed data into sheet 2, and extract it to sheet1. with the unique identifier in different order.

Sheet2

*	A	B	C	D	E	F
*
Joe	*	m	MD1	MDb1
Bob	*	m	MD2	MDb2
Tane	*	m	MD3	MDb3
Jane	*	f	MD4	MDb4

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 77px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Name[/TD]

[TD="align: center"]UniqueID[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]MoreData[/TD]
[TD="align: center"]MoreDataB[/TD]

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

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

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

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

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

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

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

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

</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4


Sheet1

*	A	B	C	D	E
name
Joe	m	MD1	MDb1
Jane	f	MD4	MDb4
Tane	m	MD3	MDb3
Bob	m	MD2	MDb2

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 77px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: center"]UniqueID[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]MoreData[/TD]
[TD="align: center"]MoreDataB[/TD]

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

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

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

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

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

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

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

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

</TBODY>

Spreadsheet Formulas

Cell Formula A2 =INDEX(Sheet2!$A$1:$F$5,MATCH($B2,Sheet2!$C$1:$C$5,0),MATCH(A$1,Sheet2!$A$1:$F$1,0)) C2 =INDEX(Sheet2!$A$1:$F$5,MATCH($B2,Sheet2!$C$1:$C$5,0),MATCH(C$1,Sheet2!$A$1:$F$1,0)) D2 =INDEX(Sheet2!$A$1:$F$5,MATCH($B2,Sheet2!$C$1:$C$5,0),MATCH(D$1,Sheet2!$A$1:$F$1,0)) E2 =INDEX(Sheet2!$A$1:$F$5,MATCH($B2,Sheet2!$C$1:$C$5,0),MATCH(E$1,Sheet2!$A$1:$F$1,0)) A3 =INDEX(Sheet2!$A$1:$F$5,MATCH($B3,Sheet2!$C$1:$C$5,0),MATCH(A$1,Sheet2!$A$1:$F$1,0)) C3 =INDEX(Sheet2!$A$1:$F$5,MATCH($B3,Sheet2!$C$1:$C$5,0),MATCH(C$1,Sheet2!$A$1:$F$1,0)) D3 =INDEX(Sheet2!$A$1:$F$5,MATCH($B3,Sheet2!$C$1:$C$5,0),MATCH(D$1,Sheet2!$A$1:$F$1,0)) E3 =INDEX(Sheet2!$A$1:$F$5,MATCH($B3,Sheet2!$C$1:$C$5,0),MATCH(E$1,Sheet2!$A$1:$F$1,0)) A4 =INDEX(Sheet2!$A$1:$F$5,MATCH($B4,Sheet2!$C$1:$C$5,0),MATCH(A$1,Sheet2!$A$1:$F$1,0)) C4 =INDEX(Sheet2!$A$1:$F$5,MATCH($B4,Sheet2!$C$1:$C$5,0),MATCH(C$1,Sheet2!$A$1:$F$1,0)) D4 =INDEX(Sheet2!$A$1:$F$5,MATCH($B4,Sheet2!$C$1:$C$5,0),MATCH(D$1,Sheet2!$A$1:$F$1,0)) E4 =INDEX(Sheet2!$A$1:$F$5,MATCH($B4,Sheet2!$C$1:$C$5,0),MATCH(E$1,Sheet2!$A$1:$F$1,0)) A5 =INDEX(Sheet2!$A$1:$F$5,MATCH($B5,Sheet2!$C$1:$C$5,0),MATCH(A$1,Sheet2!$A$1:$F$1,0)) C5 =INDEX(Sheet2!$A$1:$F$5,MATCH($B5,Sheet2!$C$1:$C$5,0),MATCH(C$1,Sheet2!$A$1:$F$1,0)) D5 =INDEX(Sheet2!$A$1:$F$5,MATCH($B5,Sheet2!$C$1:$C$5,0),MATCH(D$1,Sheet2!$A$1:$F$1,0)) E5 =INDEX(Sheet2!$A$1:$F$5,MATCH($B5,Sheet2!$C$1:$C$5,0),MATCH(E$1,Sheet2!$A$1:$F$1,0)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Excel Jeanie,

Thanks for the help. Perhaps you can push me in the right direction? When I modyfied your table and entered my own data. the Index MAtch formula appeared to work, however, when I created my own 5x4 Spreadsheet and used your formula all I got was a load of old #N/As. I am obviously missing something. Do I have to invoke some kind of add-in or, for the INDEX MATCH formula to work do I have to give a range name? As I have never seen one of these types of formulae before I do not know how they work, therfore I cannot amend it to get it to work.</SPAN>

Please note that I only have access to Office XP at work.</SPAN>

 

[konew1]

Post the formula that you tried and returned #NA. You don't need any add ins for index/match to work, and you can use either named ranges if you want, or you can put the range in the formula. I am not Jeanie. Jeanie is a software to post the sheet to the web. I can't look at this again for 3-4 days

 

[Chris Waller]

I have eventually managed to get the formula to work, I don't know what I was doing wrong, perhaps it was just one of those days. I still get a lot of #N/A where there is no data which in my opinion does not look very professional. I have tried modifying the formula as per several posts on the Internet, but at the moment to no avail.