HELP with VLOOKUP

[Guest]

Hi,

I have repeating data in column A and data assigned to that in column b. I
want to populate that data in a different cell using vlookup. but since
column A is repeating, only the first value in column B is populated.

Policy Name
4060017901 CRS Insured Name 1
4060017901 CRS Insured Name 6
4060017901 CRS Insured Name 11
4060017901 CRS Insured Name 18
4060017901 CRS Insured Name 23
4060017901 CRS Insured Name 30
4060017901 CRS Insured Name 35
4060017901 CRS Insured Name 42
4060017901 CRS Insured Name 47

I have a different worksheet with policy #'s but i need to populate the name
in that worksheet. bbut using vlookup only CRS Insured Name 1 is populated
all the time. What do i DO? plz suggest

 

[davesexcel]

Can you use the policy numbers for your criteria?

Or Have you thought of using the filter?

 

[Guest]

Yeah i am using my policy #'s as the unique field to populate the name in a
different worksheet. How is filter gonna help me tho???

 

[Roger Govier]

Hi Nick

You say you are using policy ID as the unique filed, but it is not
unique, it is repeated many times. It is only the combination of Policy
Number & Name that is unique.
I am assuming that on another sheet you have further columns of data to
the right of Name that you wish to pick up.
One way would be to insert a new column in this set of data (lets say it
is on Sheet1) at column C, and enter the following
=A1&" | "&B1
Copy down column C to the extent of your data.
Assuming your data range before was A1:G100 then your new data range to
look up would be C1:H100
Then on Sheet2, in cell C1 enter
=VLOOKUP(A1&" | "&B1,Sheet2!$C$1:$H$100,2,0)
Change ranges to suit.