Hi,

Assume you have the following data in range A1:C7. In cell A9, enter 1 or 2
or 3. In
B9, enter the following array formula (Ctrl+Shift+Enter)

=IF(ISERROR(INDEX($A$1:B$7,SMALL(IF(1*RIGHT($A$1:$ A$7,1)=$A$9,ROW($A$1:$A$7)),ROW(1:1)),COLUMN()))," ",INDEX($A$1:B$7,SMALL(IF(1*RIGHT($A$1:$A$7,1)=$A$ 9,ROW($A$1:$A$7)),ROW(1:1)),COLUMN()))

Copy this across rowes below and the column on the right.

Hope this helps. If you have any further clarifications, please feel free
to contact me.


AAAAAAA1 1000 500
BBBBBBB3 2000 200
CCCCCC1 3000 300
DDDDDD2 4000 9000
EEEEEEE1 5000 1234
FFFFFFF2 6000 2345
GGGGGGG1 7000 7890

Regards,

Ashish Mathur


"addie" wrote:


Thanks Bob. It worked.

One more question...

If I wanted to do a vlookup that incorporated that logic, what forumla
could I use?

Addie


--
addie
------------------------------------------------------------------------
addie's Profile: http://www.excelforum.com/member.php...o&userid=25526
View this thread: http://www.excelforum.com/showthread...hreadid=513868