How to extract multiple rows based on data in one column

  • Thread starter Thread starter jmlincdesk
  • Start date Start date
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?
 
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
 
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.
 
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
 
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!
 
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
 
Back
Top