Your original formula would work if you coerce the logical expression to 1
or 0:

=index(G36:G336,match(1,--(E346=left(E36:E336,9)),0))

But, matching TRUE takes one less processing cycle since you don't need to
coerce the logical expression.

=index(G36:G336,match(TRUE,E346=left(E36:E336,9),0 ))

You would use a match of 1 when there are multiple conditions:

=index(G36:G336,match(1,(E346=left(E36:E336,9))*(F 3:F336<""),0))

Multiplying the logicals together will coerce the result to a 1 or 0.

--
Biff
Microsoft Excel MVP


"JP" wrote in message
oups.com...
Thanks Biff! Kept getting an error and couldn't figure out why.

--JP

On Oct 19, 2:59 pm, "T. Valko" wrote:
{=index($G$36:$G$336,match(1,($E346=left($E$36:$G$ 336,9)),0))}

Won't work like that. Replace the 1 with TRUE.

--
Biff
Microsoft Excel MVP

"JP" wrote in message

oups.com...



Hello,

Try it as an array formula?

{=index($G$36:$G$336,match(1,($E346=left($E$36:$G$ 336,9)),0))}

HTH,
JP