Match and Concatenate ??

[carl]

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.

 

[N Harkawat]

=INDEX($c$1:$c$200&$d$1:$d$200,MATCH(F1,$A$1:$A$200,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

 

[Bob Phillips]

Assuming the table is A12:An, and the lookup data is A110

=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.

 

[Bob Phillips]

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)))

 

[N Harkawat]

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