Hi Guys,

I hope you can help. I've been trying to workout a formula to match to a certain criteria.

I have a reference unique for both sheets, in column A, need to look up to column AI only if a value in column P meets certain criteria, in this case matches a word SER.

i've tried
=IF(ISNA('[CRITICAL PATH.xlsx]CP'!$P:$P="SER"),"",VLOOKUP($A:$A,'[CRITICAL PATH.xlsx]CP'!$A:$AI,35,0))

but it returns the first available criteria rather than the first one matching SER.

I also tried

=INDEX('[CRITICAL PATH.xlsx]CP'!$AI:$AI,(MATCH(A:A,IF('[CRITICAL PATH.xlsx]CP'!$P:$P="ser",'[CRITICAL PATH.xlsx]CP'!$A:$A))))
and this:

=IF('[CRITICAL PATH.xlsx]CP'!$P:$P=DD6,INDEX('[CRITICAL PATH.xlsx]CP'!$AI:$AI,(MATCH(A:A,'[CRITICAL PATH.xlsx]CP'!$A:$A,0))))

can anyone help? i've attached shorter sample of the things i need to look up.

would appreciate any help- it's been bugging me for days!