INDEX-MATCH w/ CONCATENATE

**zamgold** · 05-22-2012, 02:48 PM

Hello,

I'm using an index-match with concatenate to extract information from a database.
I have inserted Sheet 1 in the attached, to mimic the layout in my extract file.

Please help trouble-shoot the formula in yellow-highlight.

Thanks.

**NBVC** · 05-22-2012, 02:54 PM

Try:

=IFERROR(INDEX('Operational Data(Q)'!$A$3:$IA$1000,MATCH(CONCATENATE("*",$B3,I$2),'Operational Data(Q)'!$A$3:$A$1000,0),MATCH($A$2,'Operational Data(Q)'!$A$3:$IA$3,0)),"")

**Cutter** · 05-22-2012, 03:09 PM

@NBVC
OP can't use IFERROR()

**tigeravatar** · 05-22-2012, 03:09 PM

Here's a 2003 friendly version of the formula:

=IF(AND(ISNUMBER(MATCH("*"&$B3&I$2&"*",'Operational Data(Q)'!$A$4:$A$1000,0)),ISNUMBER(MATCH($A$2,'Operational Data(Q)'!$B$3:$IA$3,0))),INDEX('Operational Data(Q)'!$B$4:$IA$1000,MATCH("*"&$B3&I$2&"*",'Operational Data(Q)'!$A$4:$A$1000,0),MATCH($A$2,'Operational Data(Q)'!$B$3:$IA$3,0)),"")

**zamgold** · 05-25-2012, 11:07 AM

Many thanks!

**NBVC** · 05-22-2012, 03:13 PM

Thanks Cutter.... when is everyone going to move up?

or:

=LOOKUP(9.9999E+307,CHOOSE({1,2},0,INDEX('Operational Data(Q)'!$A$3:$IA$1000,MATCH(CONCATENATE("*",$B3,I$2),'Operational Data(Q)'!$A$3:$A$1000,0),MATCH($A$2,'Operational Data(Q)'!$A$3:$IA$3,0))))