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
a Joe Mary Adam
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

Hi daddylonglegs,

Thanks for the quick reply! The formula worked great!

Another question:

Lets say the table looks more like this

Joe Mary Adam
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!