Upper Lookup finding nth occurance in a row returning value in corresponding column

I am using the following code to lookup the value in A10 occuring in row 2 returning the value in row 1 in the corresponding column. If the row contains multiple occurances of A10 I would like to find the nth occurance i.e. the 1st, 2nd, 3rd, 4th .......

I know there are ways of using INDEX, SMALL, MATCH and OFFSET but am struggling to get it to work.

Any ideas?


[code]
=OFFSET(B1:Q2,0, MATCH(A10, OFFSET(B1:Q2,ROWS(B1:Q2)-1,0,1,COLUMNS(B1:Q2)),0)-1,1,1)
[/code

Can you provide a sample workbook?

Hi,

If you could post a workbook with an example or two and your expected results then I'm sure we'll be able to help.

Regards

Thanks.

In R2, array formula**:

=IF(COLUMNS($A:A)>COUNTA($B2:$Q2),"",SMALL(IF($B2:$Q2=$A$10,$B$1:$Q$1),COLUMNS($A:A)))

Copy across and down as required.

Regards


**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).

You're welcome!