Excel Function help needed please!

  • Thread starter Thread starter CRWJumper
  • Start date Start date
C

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!
 
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
 
Thanks Frank!


Frank Kabel said:
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
Click to expand...
 
Back
Top