Complicated look up formula with simple calculation inserted in the formula

[Ecel Dumbo]

Hello friends,


please refer the file attached. the file is untidy. i need to look up values based on multiple conditions and insert simple calculations within look up formula.


Thanks heaps


http://speedy.sh/v9H6y/Complicated-look-up-with-formula.xls

 

[Hui]

Ecel Dumbo


You will need something like

=SUMPRODUCT((RIGHT(A10:A433,LEN(L9))=L9)*(LEFT(B10:B433,LEN(M9))=M9)*N(C10:C433))

Where:

L9 has the value Katie Cantlon

M9 has the value ZCT