MATCH - Not Auto Filter

[joe]

The results that look like those using Auto Filter is
what I want the results to look like but I can't use Auto
Filter there are too many other formulas that interact in
other areas of the worksheet. I want to be able to use
the value in one cell from another worksheet to lookup
the data below and then copy the information back onto
the first worksheet.

Thanks for your help and input
Joe

 

[Art Farrell]

Hi Joe,

Try the following formula in C1 or C2:

=INDEX($A$1:$A$100,SMALL(IF(($B$1:$B$100=Sheet1!$G$1),ROW($A$1:$A$100),""),R
OW($A1)),1)

I have assumed the value of 14 you're looking up is in Sheet1!$G$1. The
formula must be entered as an array with CTRL-SHIFT-ENTER. Then click on the
corner and drag the formula down until you get a cell with #NUM! This should
give you the matching values in column A. Formula is a takeoff of a Tom
Ogilvy answer.

CHORDially,
Art Farrell