Nested IF (with other complications)

[tradewise]

I am using the following to return an individual:

=IF(ISERROR(INDEX(B1:I36,SMALL(IF(C1:C36=B76,ROW(C1:C36)),ROW(1:1)),1)),"----------",INDEX(B1:I36,SMALL(IF(C1:C36=B76,ROW(C1:C36)),ROW(1:1)),1))

This works fine for one column (C) - but I would like it to work over the range of columns C to I.

I have tried nesting the following but have failed miserably:

=IF(ISERROR(INDEX(B1:I36,SMALL(IF(C1:C36=B76,ROW(C1:C36)),ROW(1:1)),1)),"----------",INDEX(B1:I36,SMALL(IF(C1:C36=B76,ROW(C1:C36)),ROW(1:1)),1))
=IF(ISERROR(INDEX(B1:I36,SMALL(IF(D136=B76,ROW(D136)),ROW(1:1)),1)),"----------",INDEX(B1:I36,SMALL(IF(D136=B76,ROW(D136)),ROW(1:1)),1))
=IF(ISERROR(INDEX(B1:I36,SMALL(IF(E1:E36=B76,ROW(E1:E36)),ROW(1:1)),1)),"----------",INDEX(B1:I36,SMALL(IF(E1:E36=B76,ROW(E1:E36)),ROW(1:1)),1))
=IF(ISERROR(INDEX(B1:I36,SMALL(IF(D1:F36=F76,ROW(F1:F36)),ROW(1:1)),1)),"----------",INDEX(B1:I36,SMALL(IF(F1:F36=B76,ROW(F1:F36)),ROW(1:1)),1))
=IF(ISERROR(INDEX(B1:I36,SMALL(IF(G1:G36=B76,ROW(G1:G36)),ROW(1:1)),1)),"----------",INDEX(B1:I36,SMALL(IF(G1:G36=B76,ROW(G1:G36)),ROW(1:1)),1))
=IF(ISERROR(INDEX(B1:I36,SMALL(IF(H1:H36=B76,ROW(H1:H36)),ROW(1:1)),1)),"----------",INDEX(B1:I36,SMALL(IF(H1:H36=B76,ROW(H1:H36)),ROW(1:1)),1))
=IF(ISERROR(INDEX(B1:I36,SMALL(IF(I1:I36=B76,ROW(D136)),ROW(1:1)),1)),"----------",INDEX(B1:I36,SMALL(IF(I1:I36=B76,ROW(I1:I36)),ROW(1:1)),1))

If anyone would be able to point me in the right direction, I would be most appreciative.

 

[Fenrirshowl]

If I understand this correctly, you have a table of data in B1 to I36 and you want to find and display a specific record which matches the entry in B76.

Have you looked at the Vlookup or Match functions?

 

[kjv1611]

I have a headache now from trying to read such a long formula! I say, please try to find something shorter that will do what you want to do!

If vlookup doesn't work, then maybe hlookup? Maybe just look around for some of those special formulas of the like..