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.