Lookup percentages, return names
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. |
Lookup percentages, return names
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 "360Kid" wrote in message ... 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. |
Lookup percentages, return names
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$10A$ 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 "360Kid" wrote in message ... 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. |
All times are GMT +1. The time now is 08:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com