Lookup formulas in specific columns

[Apple08]

Hi Everyone

I have two spreadsheets. Sheets(1) is with unknown number of columns and rows. Sheets(2) is a list of profiles and rates. I need to look up the specific rate in Sheets(2) for the specific profile in Sheets(1).

In Sheets(1), there are headings for 'Profile', 'Cost' and 'Rate' in three various columns and the column order is various:

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Profile[/TD]
[TD="width: 64, bgcolor: transparent"]Cost [/TD]
[TD="width: 64, bgcolor: transparent"]Rate[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Sheets(2)

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Profile[/TD]
[TD="width: 64, bgcolor: transparent"]Cost [/TD]
[TD="width: 64, bgcolor: transparent"]Rate[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent, align: right"]30[/TD]
[TD="bgcolor: transparent, align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

I need to create a VBA to look into the column 'Profile' details then lookup the specific cost and rate from Sheets(2). Please can anyone give me an idea how to create the macro? Many thanks.

 

[Gerald Higgins]

Why do you need a macro ?
Looks like basic formulas can do this, such as VLOOKUP, or INDEX/MATCH.

 

[Apple08]

I prefer a macro as the report is to be setup for another team to use and columns in the report are various due to individual customise setting from the raw data report.