Match 3 columns to one cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matching 3 columns to 1 cell - 19 May 2008 19:48 GMT

I have 9-digit account number in the first 3 columns (from A4:C65002) that I
am trying to verify if the number that I have in range D4:d54. I am trying
to use the match function to perform this, but apparently the match function
will NOT match between 3 columns only one column.

Is there a way to perform this function that I am trying to do??

Reply to this Message

Jim Cone - 19 May 2008 20:00 GMT

Try CountIf

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Matching 3 columns to 1 cell" <Matching 3 columns to 1
cell@discussions.microsoft.com>
wrote in message
I have 9-digit account number in the first 3 columns (from A4:C65002) that I
am trying to verify if the number that I have in range D4:d54. I am trying
to use the match function to perform this, but apparently the match function
will NOT match between 3 columns only one column.
Is there a way to perform this function that I am trying to do??

Reply to this Message

Otto Moehrbach - 19 May 2008 20:01 GMT

Your statement:
"I have 9-digit account number in the first 3 columns (from A4:C65002) that
I
am trying to verify if the number that I have in range D4:d54. "
isn't clear. I understand that you have 9-digit numbers in Columns A:C. I
gather that you have some numbers in the range D4:D54. What is not clear is
what do you want to do? HTH Otto

>I have 9-digit account number in the first 3 columns (from A4:C65002) that
>I
[quoted text clipped - 5 lines]
>
> Is there a way to perform this function that I am trying to do??

Reply to this Message

Jeff - 19 May 2008 20:18 GMT

To see if any numbers in columns A to C match any of the data that I have in
column D

> Your statement:
> "I have 9-digit account number in the first 3 columns (from A4:C65002) that
[quoted text clipped - 12 lines]
> >
> > Is there a way to perform this function that I am trying to do??

Reply to this Message

Jeff - 19 May 2008 20:21 GMT

The function that I have tried to use, but does NOT work is:

=MATCH(D4,$A$4:$C$65500,FALSE)

> I have 9-digit account number in the first 3 columns (from A4:C65002) that I
> am trying to verify if the number that I have in range D4:d54. I am trying
> to use the match function to perform this, but apparently the match function
> will NOT match between 3 columns only one column.
>
> Is there a way to perform this function that I am trying to do??

Reply to this Message

ryguy7272 - 19 May 2008 20:57 GMT

=SUM(N(FREQUENCY(IF(C1:C20="Yes",MATCH(A1:A20&B1:B20&C1:C20,A1:A20&B1:B20&C1:C20,0)),MATCH(A1:A20&B1:B20,A1:A20&B1:B20,0))>0))

This is an array function, so it must be entered using Ctrl+Shift+Enter (not
just enter). Hope that works for you..

Alternatively, try this
=SUM(N(FREQUENCY(IF(C1:C20="Yes",MATCH(A1:A20&B1:B20&C1:C20,A1:A20&B1:B20&C1:C20,0)),MATCH(A1:A20&B1:B20&C1:C20,A1:A20&B1:B20&C1:C20,0))>0))

Regards,
Ryan---

Signature

RyGuy

> The function that I have tried to use, but does NOT work is:
>
[quoted text clipped - 6 lines]
> >
> > Is there a way to perform this function that I am trying to do??

Reply to this Message