Lookup percentages, return names

[Guest]

I have a sheet with columns of clients and percentages. I would like a new
sheet to pull only those client names that exceed a certain percentage i.e.

50% and create a list with only those clients.

Is there a lookup function that can do this?

thanks loads.

 

[Trevor Shuttleworth]

Not sure why you need an extra sheet. Why not switch on AutoFilter and
filter on the percentage column. You could use a simple filter or a custom
filter, for example, greater than or equal to 50%

That would hide any rows that don't meet the criteria. Just print the sheet
while the filter is in. To return to the "normal" view, just choose Show
All

Regards

Trevor

 

[Biff]

Try this:

Sheet1 A2:A10 = client names
Sheet1 B2:B10 = percentage

Sheet2 A1 = percentage to use as the lookup criteria: 50%
Sheet2 A2 enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!B$2:B$10,">"&A$1),INDEX(Sheet1!A$2:A$10,SMALL(IF(Sheet1!B$2:B$10>A$1,ROW(Sheet1!A$2:A$10)-ROW(Sheet1!A$2)+1),ROWS(A$1:A1))),"")

Copy down to A10.

Here's a sample file:

sample_extract1.xls 15kb

http://cjoint.com/?kmagXYXIkn

Biff