How to use INDEX?

[Guest]

Does anyone have any suggestions on how to use INDEX on following case?
I define myRangeA by selecting columns A & N (Select A hold down Ctrl and
selectN) and name
them using insert|name, and define myRangeB by selecting columns B & O
then I can sort my RangeA under cell C1
=SMALL(myRange,row()), which give a sorted list for columns A & N

I would like to look for the value in myRangeB, when it matches the value in
column C.
I try following codes, but it does not work,
=INDEX(myRangeB, MATCH(C1, myRangeA,0)) for cell C1

Does anyone have any suggestions?
Thank for any suggestions
Eric

 

[Jim Rech]

I think you'd have to do something kludgey like this. Unlike SMALL, MATCH
and INDEX do not work on discontinuous ranges.

=IF(ISERROR(MATCH(C1,$A:$A,FALSE)),INDEX($O:$O,MATCH(C1,$N:$N,FALSE)),INDEX($B:$B,MATCH(C1,$A:$A,FALSE)))

--
Jim
| Does anyone have any suggestions on how to use INDEX on following case?
| I define myRangeA by selecting columns A & N (Select A hold down Ctrl and
| selectN) and name
| them using insert|name, and define myRangeB by selecting columns B & O
| then I can sort my RangeA under cell C1
| =SMALL(myRange,row()), which give a sorted list for columns A & N
|
| I would like to look for the value in myRangeB, when it matches the value
in
| column C.
| I try following codes, but it does not work,
| =INDEX(myRangeB, MATCH(C1, myRangeA,0)) for cell C1
|
| Does anyone have any suggestions?
| Thank for any suggestions
| Eric