Return header value from a column of data in another worksheet

[Ruth_O]

Hi,

I have 2 worksheets, I need to retrieve the column header data from one worksheet, but I have values in the row data, not text. I need to look up the ID name from my master worksheet and return the header value if of my column of data if the same ID name is in the other worksheet.

Lookups aren't working as they return the value instead of the header text, is there anyway of doing this?

Thanks,

Ruth

 

[Special-K99]

Use MATCH to find the data which will return a column.
Use INDEX on that column on the header row to retrieve the header value.
For example

Sheet1
CAT DOG MOUSE
1   2   3

=INDEX(Sheet1!A$1:C$1,1,MATCH(Sheet2!A1,Sheet1!A$2:C$2,0))

where Sheet2!A1 is the lookup value 1 or 2 or 3.

 

[Ruth_O]

Thanks for the reply,

I need the formula to look up and return the same value in both the worksheets, but only the header:

[TABLE="width: 658"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]CDE Assassin SDS Customer Jetbridge
01 - Bank 0 2 0 0


[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 658"]
<tbody>[TR]
[TD]The text in bold is the unique value across both data sets which I need to lookup to and return the column header value

Can I use the Index/Match formula to retrieve this?

Thanks,

Ruth







[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Use MATCH to find the data which will return a column.
Use INDEX on that column on the header row to retrieve the header value.
For example

Sheet1
CAT DOG MOUSE
1   2   3

=INDEX(Sheet1!A$1:C$1,1,MATCH(Sheet2!A1,Sheet1!A$2:C$2,0))

where Sheet2!A1 is the lookup value 1 or 2 or 3.

 

[Special-K99]

Yep just adjust the formula I posted.

Change the lookup value in the MATCH to reflect your lookup value.
Change the range in the INDEX to reflect your data range.

I have no cell references or ranges to work on as you haven't provided any.
It's ok saying I have some data sets but without specific cells references/ranges all I can provide an example solution that works.
Then it's over to you to modify it to your needs.

Or use these tools to show where the data is specifically on each sheet.

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Ruth_O]

Sorry works internet wont let my view how to attach to the forum:

Small Extract of both data sets below:

Worksheet 1:

CDE Assassin SDS Customer Jetbridge
01 - Bank 0 2 0 0
AA - As Agent 0 2 0 0


Worksheet 2:

CDE Definition Catalogue DQ ADS
01 - Bank Counterparty is a bank Regulatory Need header value in sheet1 here
AA - As Legal





Sorry, I can't get it to work using INDEX/MATCH:

Thanks for your help,

Ruth

Yep just adjust the formula I posted.

Change the lookup value in the MATCH to reflect your lookup value.
Change the range in the INDEX to reflect your data range.

I have no cell references or ranges to work on as you haven't provided any.
It's ok saying I have some data sets but without specific cells references/ranges all I can provide an example solution that works.
Then it's over to you to modify it to your needs.

Or use these tools to show where the data is specifically on each sheet.

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Sam_D_Ben]

Team, Is this OP looking for ?

Excel 2013/2016
	A
1	Test
2	1
3	2
4	3
Sheet1

Excel 2013/2016
	A	B
2	3	Test
Sheet2

Cell Formulas
Range	Formula
B2	=INDEX(Sheet1!$A$1:$A$4,1,(MATCH(MAX(A2),A2,0)))

 

[Sam_D_Ben]

Or,

Excel 2013/2016
	A
1	Test
2	1
3	2
4	3
Sheet1

Excel 2013/2016
	A	B
1
2	3	Test
Sheet2

Cell Formulas
Range	Formula
B2	{=INDEX(Sheet1!A1:A4,,SUMPRODUCT((Sheet1!A1:A4=A2)*(COLUMN(Sheet1!A1:A4)-COLUMN(Sheet1!$A$1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sam_D_Ben]

Sorry works internet wont let my view how to attach to the forum:

Small Extract of both data sets below:

Worksheet 1:

CDE Assassin SDS Customer Jetbridge
01 - Bank 0 2 0 0
AA - As Agent 0 2 0 0


Worksheet 2:

CDE Definition Catalogue DQ ADS
01 - Bank Counterparty is a bank Regulatory Need header value in sheet1 here
AA - As Legal





Sorry, I can't get it to work using INDEX/MATCH:

Thanks for your help,

Ruth

Oh sorry, I dint see this.