Find second result

[Pat]

The following formula will find the first result only, does anyone know if
it can look only for the second result. Is this possible?

=INDEX(sheet1!$F$7:$F$100,MATCH(sheet2!$C76,sheet1!$R$7:$R$100,0))

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):

=INDEX(sheet1!$F$7:$F$100,SMALL(IF((sheet2!$C76=sheet1!$R$7:$R$100),ROW
(sheet1!$R$7:$R$100)),2))

 

[Pat]

For whatever reason I am getting #NUM! error.

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):

=INDEX(sheet1!$F$7:$F$100,SMALL(IF((sheet2!$C76=sheet1!$R$7:$R$100),ROW
(sheet1!$R$7:$R$100)),2))

 

[Frank Kabel]

Hi
what is the exact formula you have used and do you have two valid
entries in your data range?

 

[Pat]

We some playing around testing different theories I managed to get it to
partly work.
#NUM! error will display if there is no second result to display. I think
error checking is needed in the formula. The change to the formula is an
attempt to fix it but as will see is not correct.

=IF(ISERROR(INDEX(sheet1!$F$1:$F$100,SMALL(IF((sheet2!$C76=sheet1!$R$1:$R$10
0),ROW(sheet1!$R$1:$R$100)),2)),"",INDEX(sheet1!$F$1:$F$100,SMALL(IF((sheet2
!$C76=sheet1!$R$1:$R$100),ROW(sheet1!$R$1:$R$100)),2))

One thing that I discovered when trying to make it work is that the range
should start at row 1 and not 7. What happens is that the result displayed
is the 7th cell below the real answer. You will see I have changed this
above.

Pat