Index and Sumproduct problem

[missbb]

HI, I would like to use index and sumproduct for the attached excel but not work. Please help to advise, thanks.

INDEX($A$3:$E$3,SUMPRODUCT(($B$4:$E$5=H2)*(ROW($A$4:$A$5)-2)))

[AliGW]

The formula is intended to work with exact matches, not partial ones.

You need to explain what you are trying to do, as your set-up is completely different.

[bebo021999]

Try:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[XLent]

to avoid risk of false positives - e.g. Paul and Pauline -- it's a good idea to delimit both criteria (I3 etc) and source range, i.e.

Please Login or Register  to view this content.

[missbb]

Hi, AligW, I want to insert the "rank" at I2:I8 for staff H2:H8 from the table (B4:E5), staff ranking row B3:E3 (i.e. 1,2,3 or 4). However, like cell C4, some cell will have over 1 staff group together.

[missbb]

Dear Bebo021999 and XLent, thank you very much and I have learn a lot of excel techniques.