# Index/Match Function to Return column header

1. ## Index/Match Function to Return column header

Hello

I would like help with the following question

for the following table in sheet 1 A2:D5

1 2 3
b Mike Erin Steve
c Ann Ken Jill

In sheet 2 Iets assume I have the following in a row

b apple cat Brown Steve

I woudl like a formula that takes "b" and "Steve" from Sheet 2 then goes to Sheet 1 and looks for the row that has "b" then looks for "Steve" in that row data then returns 3 as a result (Column Heading)

Please note on Sheet 1 I have 5000 rows of data and and Sheet 2 I have another 5000 rows and about 100 columns, so I will be copying and pasting the formula many times.

Is this possible?

Thanks for the help.

You can use a formula like this

=INDEX(Sheet1!\$B\$1:\$D\$1,MATCH(E2,INDEX(Sheet1!\$B\$2:\$D\$4,MATCH(A2,Sheet1!\$A\$2:\$A\$4,0),0),0))

where "b" is in A2 and "Steve" in E2

see attached F2 copied down

3. ## Re: Index/Match Function to Return column header

Thanks for the quick reply! The formula worked great!

Another question:

Lets say the table looks more like this

a 2 9 3
b 3 6 9
c 5 15 6

A bit similar to the above formulab ut changing it a bit I would like a formula that takes "a" and "2" from Sheet 2 then goes to Sheet 1 and looks for the row that has "a" then does not take the "2" but the next closest value to "2" being "3" returns "Adam" as a result (Column Heading)

Is it possible to Modify this formula to do this?

=INDEX(Sheet1!\$B\$1:\$D\$1,MATCH(E2,INDEX(Sheet1!\$B\$2:\$D\$4,MATCH(A2,Sheet1!\$A\$2:\$A\$4,0),0),0))

Thanks again for the help!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1