Thread: V Look Up Case

I am trying to V Look Up a schedule from another excel source to get AC & Invoice but each of the conditions varies.

I could not V Look Up cell by cell as the full name of the cell never matches the source cell, i.e. Data : TEACREATE PLC 12494/OB0010 BGC and Source : TEACREATE . So I set aside in a different column the first 6-7 letters for V Look Up purposes.

I have displayed below the Data with the conditions in the attachment & highlighted in different colours of the information needed from the Source.

I need situations like :

A - match as per name = MATCH = then match the amt = MATCH = give me SIN & a/c
B - match as per name =MATCH = then match the amt = NO = then look for the same name = MATCH =then match the amt = MATCH = give me SIN & a/c (several entitties)
C - match as per name = NO = N/A
D - match as per name =MATCH = then match the amt = NO = then look for the same name = MATCH = then match the amt = NO =TRY total up few numbers to match the amt = YES MATCH = give me Sin & a/c

I am not sure whether situation D is possible. Could you please help me on figuring this out? I am not a pro user of Excel but I need a fixed formula/ coding for this case.


Thanks