Excel Function help needed please!

[CRWJumper]

I have an Excel spreadsheet with several worksheets from which I need
to compare values from several columns in sheet one to a single column
in sheet two, and return the value contained within one cell (a
"title" at the top of the column)in sheet one to an adjacent cell to
the original value cell within sheet two. Can anyone help me to
figure this one out?

Thanks!

 

[Frank Kabel]

Hi
try the following:
Assumptions:
- sheet one has a heading row (row 1) and consists of columns A:F
- sheet two has its data stored in column A (starting in row 1) and you
want to place the column heading of the matching value from sheet 1 in
column B)

Enter the following in B1 (on sheet 2):
=IF(SUMPRODUCT(MAX(($A$2:$F$1000=B1)*(COLUMN($A$2:$F$1000))))>0,INDEX('
sheet1'!$A$1:$F$1,SUMPRODUCT(MAX(($A$2:$F$1000=B1)*(COLUMN($A$2:$F$1000
))))),"")
and copy down

 

[CRWJumper]

Thanks Frank!

Hi
try the following:
Assumptions:
- sheet one has a heading row (row 1) and consists of columns A:F
- sheet two has its data stored in column A (starting in row 1) and you
want to place the column heading of the matching value from sheet 1 in
column B)

Enter the following in B1 (on sheet 2):
=IF(SUMPRODUCT(MAX(($A$2:$F$1000=B1)*(COLUMN($A$2:$F$1000))))>0,INDEX('
sheet1'!$A$1:$F$1,SUMPRODUCT(MAX(($A$2:$F$1000=B1)*(COLUMN($A$2:$F$1000
))))),"")
and copy down