Match and Concatenate ??
 

My data looks as so:

Data ColA ColB ColC ColD
A 123 Zebra Monkey
B 456 Dog Cat
C 123 Mouse Rabbit
A 789 Horse Cow
C 789 Cat Dog

I'm trying to put an array formula into ColB in the table below that will
look in
A1 (in the table below) and find a match in ColB of the data above, AND if
ColA of the data above is equal to"A", concatenate the values in ColC and
ColD - as shown below.

Table
123 ZebraMonkey
456
789 HorseCow

So the formula needs to find a match to cell A1 in the table in ColB of the
data, and then check if the adjacent value in ColA of the data is equal to
"A", concatenate the adjacent values in ColC and ColD of the data.

Is it possible ? Thank you in advance.

=INDEX($c$1:$c$200&$d$1:$d$200,MATCH(F1,$A$1:$A$20 0,0))
where a1:d 200 is the source range
where cell F1 holds the value that you want looked up and in your exampple
values like 123,456,789
Adjust your cells ranges accordingly , you get the idea


"carl" wrote in message
...


My data looks as so:

Data ColA ColB ColC ColD
A 123 Zebra Monkey
B 456 Dog Cat
C 123 Mouse Rabbit
A 789 Horse Cow
C 789 Cat Dog

I'm trying to put an array formula into ColB in the table below that will
look in
A1 (in the table below) and find a match in ColB of the data above, AND if
ColA of the data above is equal to"A", concatenate the values in ColC and
ColD - as shown below.

Table
123 ZebraMonkey
456
789 HorseCow

So the formula needs to find a match to cell A1 in the table in ColB of
the
data, and then check if the adjacent value in ColA of the data is equal to
"A", concatenate the adjacent values in ColC and ColD of the data.

Is it possible ? Thank you in advance.

Assuming the table is A12:An, and the lookup data is A1:D10

=IF(ISNUMBER(MATCH(A12&"A",$B$2:$B$10&$A$2:$A$10,0 )),INDEX($C$2:$C$10,MATCH(
A12&"A",$B$2:$B$10&$A$2:$A$10,0))&INDEX($D$2:$D$10 ,MATCH(A12&"A",$B$2:$B$10&
$A$2:$A$10,0)),"")

This is an array formula, so c ommit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"carl" wrote in message
...


My data looks as so:

Data ColA ColB ColC ColD
A 123 Zebra Monkey
B 456 Dog Cat
C 123 Mouse Rabbit
A 789 Horse Cow
C 789 Cat Dog

I'm trying to put an array formula into ColB in the table below that will
look in
A1 (in the table below) and find a match in ColB of the data above, AND if
ColA of the data above is equal to"A", concatenate the values in ColC and
ColD - as shown below.

Table
123 ZebraMonkey
456
789 HorseCow

So the formula needs to find a match to cell A1 in the table in ColB of
the
data, and then check if the adjacent value in ColA of the data is equal to
"A", concatenate the adjacent values in ColC and ColD of the data.

Is it possible ? Thank you in advance.

Your formula is more concise than mine, but I think it needs a bit more, to
cater for the A and #N/A

=IF(NOT(ISNUMBER(MATCH(A12&"A",$B$1:$B$200&$A$1:$A $200,0))),"",INDEX($C$1:$C
$200&$D$1:$D$200,MATCH(F1&"A",$B$1:$B$200&$A$1:$A$ 200,0)))

--
HTH

Bob Phillips

"N Harkawat" <nharkawat@hotmail_dot_com wrote in message
...

=INDEX($c$1:$c$200&$d$1:$d$200,MATCH(F1,$A$1:$A$20 0,0))
where a1:d 200 is the source range
where cell F1 holds the value that you want looked up and in your exampple
values like 123,456,789
Adjust your cells ranges accordingly , you get the idea


"carl" wrote in message
...


My data looks as so:

Data ColA ColB ColC ColD
A 123 Zebra Monkey
B 456 Dog Cat
C 123 Mouse Rabbit
A 789 Horse Cow
C 789 Cat Dog

I'm trying to put an array formula into ColB in the table below that
will
look in
A1 (in the table below) and find a match in ColB of the data above, AND
if
ColA of the data above is equal to"A", concatenate the values in ColC
and
ColD - as shown below.

Table
123 ZebraMonkey
456
789 HorseCow

So the formula needs to find a match to cell A1 in the table in ColB of
the
data, and then check if the adjacent value in ColA of the data is equal
to
"A", concatenate the adjacent values in ColC and ColD of the data.

Is it possible ? Thank you in advance.

please ignore the precious one
this is the correct formula
=INDEX($C$1:$C$200&$D$1:$D$200,MATCH("A"&F1,A1:A20 0&$B$1:$B$200,0))
array entered (ctrl+shift+enter)

"N Harkawat" <nharkawat@hotmail_dot_com wrote in message
...

=INDEX($c$1:$c$200&$d$1:$d$200,MATCH(F1,$A$1:$A$20 0,0))
where a1:d 200 is the source range
where cell F1 holds the value that you want looked up and in your exampple
values like 123,456,789
Adjust your cells ranges accordingly , you get the idea


"carl" wrote in message
...


My data looks as so:

Data ColA ColB ColC ColD
A 123 Zebra Monkey
B 456 Dog Cat
C 123 Mouse Rabbit
A 789 Horse Cow
C 789 Cat Dog

I'm trying to put an array formula into ColB in the table below that will
look in
A1 (in the table below) and find a match in ColB of the data above, AND
if
ColA of the data above is equal to"A", concatenate the values in ColC
and
ColD - as shown below.

Table
123 ZebraMonkey
456
789 HorseCow

So the formula needs to find a match to cell A1 in the table in ColB of
the
data, and then check if the adjacent value in ColA of the data is equal
to
"A", concatenate the adjacent values in ColC and ColD of the data.

Is it possible ? Thank you in advance.