Looking up values within multiple cells

[Aberdham]

Hi Excel Gurus,

I am currently working on a Project that requires me to look up the respective value: Here is how it goes,

I have got 2 workbooks, one is the master workbook that contains data such as:
Workbook 1

[TABLE="width: 470"]
<tbody>[TR]
[TD="align: right"]Serial Number[/TD]
[TD="align: right"]Contract fee[/TD]
[TD="align: right"]Maintanence fee[/TD]
[TD="align: right"]Contract cost[/TD]
[TD="align: right"]Maintanence cost[/TD]
[/TR]
[TR]
[TD="align: right"]545256[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]545254[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]545255[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]156985[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]898985C

and workbook2:










[/TD]
[TD="align: right"][/TD]
[TD="align: right"][TABLE="width: 721"]
<tbody>[TR]
[TD="align: left"]serial number[/TD]
[TD="align: left"]Invoice number[/TD]
[TD="align: left"]description[/TD]
[TD="align: left"]amount[/TD]
[TD="align: left"][/TD]
[TD="align: left"]serial[/TD]
[TD="align: left"]invoice number[/TD]
[TD="align: left"]description[/TD]
[TD="align: left"]amount[/TD]
[/TR]
[TR]
[TD="align: left"]545256[/TD]
[TD="align: left"]16CD[/TD]
[TD="align: left"]contract fee[/TD]
[TD="align: left"],,,[/TD]
[TD="align: left"][/TD]
[TD="align: left"]545256[/TD]
[TD="align: left"]RANDOM[/TD]
[TD="align: left"]contract fee[/TD]
[TD="align: left"],,,[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]16MD[/TD]
[TD="align: left"]Maintanence fee[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]545254[/TD]
[TD="align: left"][/TD]
[TD="align: left"]Maintanence fee[/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]16DC[/TD]
[TD="align: left"]credit memo[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]545255[/TD]
[TD="align: left"][/TD]
[TD="align: left"]credit memo[/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"]545254[/TD]
[TD="align: left"]16CD[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]156985[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD="align: left"]16MD[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]898985C[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"]545255[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"],,,[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"]156985[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]898985C[/TD]
[TD="align: left"][/TD]
[TD="align: left"](Fee)[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[TD="align: left"](Cost)[/TD]
[TD="align: left"][/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"],,,[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Could any of you help me come up with a lookup formula or VBA that can do the job?

Would be much appreciated!


Edit: And I would like to have the data (amount) from workbook2 to Transfer to workbook 1

Thanks!

 

[Aberdham]

Anyone could help? I will just wait here... Hope to hear from you soon !

 

[Aberdham]

My Idea is to first look for the matching Serial number in workbook 2 as to workbook 1; and then look for the respecitive description whether it is a contract fee or a maintenance fee, and then the amount in workbook 2 goes to workbook 1 respectively. perhaps a if and lookup formula might help? and set some condition with the contract fee or maintanence fee?