How to extract multiple rows based on data in one column

J

jmlincdesk

I work at an insurance brokerage where we can download payment
statements in an Excel format. We would like to search a list of
policy numbers (as a group vs. individually) and then extract the
entire row the policy number falls in into another spreadsheet. Is
there a way to do this?
 
P

Pete_UK

You would normally use VLOOKUP to do this, assuming the policy number
is in the first column of your data - if not, then you would use and
INDEX/MATCH combination. However, both VLOOKUP and MATCH will only
find the first match - I'm a bit confused as your heading talks about
extracting multiple rows, so do you have more than one row per policy
number?

Hope this helps.

Pete
 
J

jmlincdesk

Thanks for the reply. Sorry for the confusing heading........Noob!
Anyway, each row lists a policy with names and commission. Is there a
way to use VLOOKUP or MATCH to search a group of values- the policy
numbers that are desired to be separated onto another spread sheet-
and then once found extract the entire row? Does this make any
sense???? Thanks again.
 
P

Pete_UK

Can you tell me what columns you have in your existing data, and how
many rows of data does it occupy?

I assume that you will list the policy numbers of interest in column A
of Sheet2 starting with A2 (to allow for a header row), and I'll be
able to give you a formula (either VLOOKUP of INDEX/MATCH) to put in
B2 which can be copied across and down to extract all the data for
those policy numbers.

Pete
 
J

jmlincdesk

That would be sweet!!!!! Ok, the commission sheet has columns 'A-K'
filled with information. Column 'C' contains the policy numbers. THANK
YOU again for your help!
 
P

Pete_UK

Okay, with your list of policy numbers in column A of Sheet2, put this
formula in B2:

=IF(A2="","",INDEX(Sheet1:A:A,MATCH(A2,Sheet1!C:C,0)))

and this one in C2:

=IF(A2="","",INDEX(Sheet1:B:B,MATCH(A2,Sheet1!C:C,0)))

These will bring the corresponding data from columns A and B of Sheet1
respectively. For the other columns we can use VLOOKUP - put this
formula in D2 of Sheet2:

=IF(A2="","",VLOOKUP($A2,Sheet1!$C:$K,COLUMN(B1),0))

and copy this formula across into E2:K2. You will then have all the
data for that policy number copied across into sheet2. Then you can
copy B2:K2 down for as many rows as you think you need.

Hope this helps.

Pete
 
Top