Help with An Array Formula and Duplicates
 

I have a list of data that looks like the following. Column A has a sec type
and Column B has it's corresponding cusip. In Column C is a smaller list of
cusips that I need to compare to Column B to check for duplicates. I then
need Column D to pull the sec type that goes with the duplicate cusip. I
tried an INDEX array formula which can find and list the duplicates in D but
I don't know how to make it return the corresponding value from Column A.
ColumnA - Column B - Column C Column D
vgus equ01234 equ03245 (I want this to return "vbus")
vbus equ03245 equ09846 (I want this to return "vmus")
vvus equ01111 equ22222 (I want this to return "vxus)
vmus equ09846
vxus equ22222
Thanks for the help!!

Help with An Array Formula and Duplicates
 

Try this:

=INDEX(A$1:A$5,MATCH(C1,B$1:B$5,0))

Copy down as needed

--
Biff
Microsoft Excel MVP


"jen" wrote in message
...
I have a list of data that looks like the following. Column A has a sec
type
and Column B has it's corresponding cusip. In Column C is a smaller list
of
cusips that I need to compare to Column B to check for duplicates. I then
need Column D to pull the sec type that goes with the duplicate cusip. I
tried an INDEX array formula which can find and list the duplicates in D
but
I don't know how to make it return the corresponding value from Column A.
ColumnA - Column B - Column C Column D
vgus equ01234 equ03245 (I want this to return "vbus")
vbus equ03245 equ09846 (I want this to return "vmus")
vvus equ01111 equ22222 (I want this to return "vxus)
vmus equ09846
vxus equ22222
Thanks for the help!!

Help with An Array Formula and Duplicates
 

THANK YOU! It works!

"jen" wrote:

I have a list of data that looks like the following. Column A has a sec type
and Column B has it's corresponding cusip. In Column C is a smaller list of
cusips that I need to compare to Column B to check for duplicates. I then
need Column D to pull the sec type that goes with the duplicate cusip. I
tried an INDEX array formula which can find and list the duplicates in D but
I don't know how to make it return the corresponding value from Column A.
ColumnA - Column B - Column C Column D
vgus equ01234 equ03245 (I want this to return "vbus")
vbus equ03245 equ09846 (I want this to return "vmus")
vvus equ01111 equ22222 (I want this to return "vxus)
vmus equ09846
vxus equ22222
Thanks for the help!!

Help with An Array Formula and Duplicates
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"jen" wrote in message
...
THANK YOU! It works!

"jen" wrote:

I have a list of data that looks like the following. Column A has a sec
type
and Column B has it's corresponding cusip. In Column C is a smaller list
of
cusips that I need to compare to Column B to check for duplicates. I
then
need Column D to pull the sec type that goes with the duplicate cusip. I
tried an INDEX array formula which can find and list the duplicates in D
but
I don't know how to make it return the corresponding value from Column A.
ColumnA - Column B - Column C Column D
vgus equ01234 equ03245 (I want this to return "vbus")
vbus equ03245 equ09846 (I want this to return "vmus")
vvus equ01111 equ22222 (I want this to return "vxus)
vmus equ09846
vxus equ22222
Thanks for the help!!