Prev: VLookup with Search for in String
Next: Calculate Number of Days worked in Each Month by Project
From: Zuo on 23 Mar 2010 18:39
Hi,

I have two sets of data, one in A1:A450 and the other in D1:D700. I want
the matching data (reference) of the array in column A to appear in Column C
or next to the matched value of column D.

Example:

A B C D
23 14
12 52
37 89
45 23 23

Thanks in advance for your help.


From: Max on 23 Mar 2010 19:12
In E1: =IF(ISNA(MATCH(D1,A:A,0)),"","A"&MATCH(D1,A:A,0))
Copy down to return col A's cell references where col A match col D. Eg you
will get "A1" returned in E4 for your sample data. Any good? hit the YES
below
--
Max
Singapore
---
"Zuo" wrote:
> I have two sets of data, one in A1:A450 and the other in D1:D700. I want
> the matching data (reference) of the array in column A to appear in Column C
> or next to the matched value of column D.
>
> Example:
>
> A B C D
> 23 14
> 12 52
> 37 89
> 45 23 23
>
> Thanks in advance for your help.
>
>
From: Zuo on 23 Mar 2010 19:33
Max,

Almost there but I want the actual matching reference that is sitting in A1
to appear in E4 next to its match on D4. Not the cell location but the
actual data. i.e. if A1=23 and D4 =23 then 23 should appear in E4 or next to
D4.

Thanks.

"Max" wrote:

> In E1: =IF(ISNA(MATCH(D1,A:A,0)),"","A"&MATCH(D1,A:A,0))
> Copy down to return col A's cell references where col A match col D. Eg you
> will get "A1" returned in E4 for your sample data. Any good? hit the YES
> below
> --
> Max
> Singapore
> ---
> "Zuo" wrote:
> > I have two sets of data, one in A1:A450 and the other in D1:D700. I want
> > the matching data (reference) of the array in column A to appear in Column C
> > or next to the matched value of column D.
> >
> > Example:
> >
> > A B C D
> > 23 14
> > 12 52
> > 37 89
> > 45 23 23
> >
> > Thanks in advance for your help.
> >
> >
From: Zuo on 23 Mar 2010 20:26
Max,

Any thoughts on how to address my comment below?

Regards,

ZUO

"Zuo" wrote:

> Max,
>
> Almost there but I want the actual matching reference that is sitting in A1
> to appear in E4 next to its match on D4. Not the cell location but the
> actual data. i.e. if A1=23 and D4 =23 then 23 should appear in E4 or next to
> D4.
>
> Thanks.
>
> "Max" wrote:
>
> > In E1: =IF(ISNA(MATCH(D1,A:A,0)),"","A"&MATCH(D1,A:A,0))
> > Copy down to return col A's cell references where col A match col D. Eg you
> > will get "A1" returned in E4 for your sample data. Any good? hit the YES
> > below
> > --
> > Max
> > Singapore
> > ---
> > "Zuo" wrote:
> > > I have two sets of data, one in A1:A450 and the other in D1:D700. I want
> > > the matching data (reference) of the array in column A to appear in Column C
> > > or next to the matched value of column D.
> > >
> > > Example:
> > >
> > > A B C D
> > > 23 14
> > > 12 52
> > > 37 89
> > > 45 23 23
> > >
> > > Thanks in advance for your help.
> > >
> > >
From: Max on 23 Mar 2010 21:55
> ... Not the cell location but the actual data ..
It's even simpler then ..
In E1, copied down: =IF(ISNA(MATCH(D1,A:A,0)),"",D1)
Success? hit the YES below
--
Max
Singapore
---
> "Zuo" wrote:
> >
> > Almost there but I want the actual matching reference that is sitting in A1
> > to appear in E4 next to its match on D4. Not the cell location but the
> > actual data. i.e. if A1=23 and D4 =23 then 23 should appear in E4 or next to
> > D4.
> >
> > Thanks.

 |  Next  |  Last
Pages: 1 2
Prev: VLookup with Search for in String
Next: Calculate Number of Days worked in Each Month by Project