Return header value from a column of data in another worksheet

Ruth_O

New Member
Joined
Sep 9, 2019
Messages
9
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

Code:
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.
 
Last edited:
Upvote 0
Thanks for the reply,

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

CDE Assassin SDS Customer Jetbridge
01 - Bank 0 2 0 0


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








<tbody>
</tbody>
0200

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>



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

Code:
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Team, Is this OP looking for ?


Excel 2013/2016
A
1Test
21
32
43
Sheet1



Excel 2013/2016
AB
23Test
Sheet2
Cell Formulas
RangeFormula
B2=INDEX(Sheet1!$A$1:$A$4,1,(MATCH(MAX(A2),A2,0)))
 
Last edited:
Upvote 0
Or,


Excel 2013/2016
A
1Test
21
32
43
Sheet1



Excel 2013/2016
AB
1
23Test
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top