Data Matching
 

Hello,

I have a question. I have two sets of data - one large and one small. The
large one has metadata, and the small one fits entirely inside the large one.
I need to separate out the metadata from the large set for just the entries
in the small set. For example, say I have these three columns:

abc 123 123
bcd 234 567
cde 456 678
def 567 901
efg 678 012
fgh 789
ghi 890
hij 901
ijk 012
jkl 321
klm 432

Where column A is the metadata, column B is the large set, and column C is
the small set. I want to create another column (or two columns) that match
the data in column C with that in column A. So the output that I want looks
like this:

abc 123
def 567
efg 678
hij 901
ijk 012

In other words, I want the metadata from column A to match with the data in
column C. I can't just do a sort, because column C is missing some numbers.
Does anyone have any thoughts for how to do this?

Thanks for your help,

Data Matching
 

Demosthenes -

If you can switch the order of columns A and B, then a simple VLOOKUP will
do what you want. Assuming data starts in row 2, in cell D2, you would have:

VLOOKUP(C2, A$2:B$12,2,FALSE)

Change the B12 to be the last cell in the column B, and then copy/paste or
drag this formula down for all the values in column C.

(If you can't change the order of columns A and B, then copy/paste them
somewhere else with the new order, and base your VLOOKUP on the copied cells)
--
Daryl S


"Demosthenes" wrote:

Hello,

I have a question. I have two sets of data - one large and one small. The
large one has metadata, and the small one fits entirely inside the large one.
I need to separate out the metadata from the large set for just the entries
in the small set. For example, say I have these three columns:

abc 123 123
bcd 234 567
cde 456 678
def 567 901
efg 678 012
fgh 789
ghi 890
hij 901
ijk 012
jkl 321
klm 432

Where column A is the metadata, column B is the large set, and column C is
the small set. I want to create another column (or two columns) that match
the data in column C with that in column A. So the output that I want looks
like this:

abc 123
def 567
efg 678
hij 901
ijk 012

In other words, I want the metadata from column A to match with the data in
column C. I can't just do a sort, because column C is missing some numbers.
Does anyone have any thoughts for how to do this?

Thanks for your help,

Data Matching
 

Try index/match. Its more versatile - match on any col, return any other col
to the left/right of the match col
Assume source data as you posted is in A2:C2 down
In D2: =IF(ISNA(MATCH(C2,B:B,0)),"",INDEX(A:A,MATCH(C2,B: B,0)))
In E2: =IF(ISNA(MATCH(C2,B:B,0)),"",C2)
Copy D2:E2 down. voila? celebrate it, hit the YES below
--
Max
Singapore
---
"Demosthenes" wrote:
I have a question. I have two sets of data - one large and one small. The
large one has metadata, and the small one fits entirely inside the large one.
I need to separate out the metadata from the large set for just the entries
in the small set. For example, say I have these three columns:

abc 123 123
bcd 234 567
cde 456 678
def 567 901
efg 678 012
fgh 789
ghi 890
hij 901
ijk 012
jkl 321
klm 432

Where column A is the metadata, column B is the large set, and column C is
the small set. I want to create another column (or two columns) that match
the data in column C with that in column A. So the output that I want looks
like this:

abc 123
def 567
efg 678
hij 901
ijk 012

In other words, I want the metadata from column A to match with the data in
column C. I can't just do a sort, because column C is missing some numbers.
Does anyone have any thoughts for how to do this?

Thanks for your help,