Find and Replace with lookup table with mutiple conditions (Categories)

[Shayla19]

I have two sheets: the first with data with column headers as 'Line#s' I need to change based off of a lookup table on the second sheet. Both tables have an overall category (ID) and sub-category (Type). The header names in the lookup table vary depending both categories. I need a condition statement to choose the correct value that looks at the category values and make sure they match. My first table looks something like this:
[TABLE="width: 300"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Type
[/TD]
[TD]Line13
[/TD]
[TD]Line17
[/TD]
[/TR]
[TR]
[TD]421
[/TD]
[TD]B
[/TD]
[TD]123
[/TD]
[TD]234
[/TD]
[/TR]
[TR]
[TD]421
[/TD]
[TD]B
[/TD]
[TD]456
[/TD]
[TD]124
[/TD]
[/TR]
</tbody>[/TABLE]
The lookup table is large with values for multiple ID's and Types like follows:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Type
[/TD]
[TD]Line #
[/TD]
[TD]Gas
[/TD]
[/TR]
[TR]
[TD]421
[/TD]
[TD]A
[/TD]
[TD]13
[/TD]
[TD]H2O
[/TD]
[/TR]
[TR]
[TD]421
[/TD]
[TD]A
[/TD]
[TD]17
[/TD]
[TD]CO2
[/TD]
[/TR]
[TR]
[TD]421
[/TD]
[TD]B
[/TD]
[TD]13
[/TD]
[TD]H2
[/TD]
[/TR]
[TR]
[TD]421
[/TD]
[TD]B
[/TD]
[TD]17
[/TD]
[TD]CO2
[/TD]
[/TR]
[TR]
[TD]421
[/TD]
[TD]C
[/TD]
[TD]17
[/TD]
[TD]O2
[/TD]
[/TR]
[TR]
[TD]556
[/TD]
[TD]A
[/TD]
[TD]13
[/TD]
[TD]N20
[/TD]
[/TR]
[TR]
[TD]556
[/TD]
[TD]B
[/TD]
[TD]13
[/TD]
[TD]H2
[/TD]
[/TR]
[TR]
[TD]556
[/TD]
[TD]C
[/TD]
[TD]13
[/TD]
[TD]CO2
[/TD]
[/TR]
</tbody>[/TABLE]
Therefore the result should find the corresponding gas name for the Line # and replace it looking like this:
[TABLE="width: 300"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Type[/TD]
[TD]H2
[/TD]
[TD]CO2
[/TD]
[/TR]
[TR]
[TD]421
[/TD]
[TD]B
[/TD]
[TD]123
[/TD]
[TD]234
[/TD]
[/TR]
[TR]
[TD]421
[/TD]
[TD]B
[/TD]
[TD]456
[/TD]
[TD]124
[/TD]
[/TR]
</tbody>[/TABLE]

The incoming data usually has a single ID with multiple types but I have acquired code to break up the data into multiple sheets each with one ID and one Type to make the column header change valid.

Help would be tremendously appreciated! I am new to VBA and do not understand the language

 

[oldbrewer]

[TABLE="width: 1152"]
<colgroup><col span="18"></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Type[/TD]
[TD]Line13[/TD]
[TD]Line17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]421[/TD]
[TD]B[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]421[/TD]
[TD]B[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]124[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]The lookup table is large with values for multiple ID's and Types like follows:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID
[/TD]
[TD]Type[/TD]
[TD]Line #[/TD]
[TD]Gas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]421[/TD]
[TD]A[/TD]
[TD="align: right"]13[/TD]
[TD]H2O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]421A13[/TD]
[TD][/TD]
[TD]row 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]421[/TD]
[TD]A[/TD]
[TD="align: right"]17[/TD]
[TD]CO2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]421A17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]421[/TD]
[TD]B[/TD]
[TD="align: right"]13[/TD]
[TD]H2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]421B13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]421[/TD]
[TD]B[/TD]
[TD="align: right"]17[/TD]
[TD]CO2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]421B17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]421[/TD]
[TD]C[/TD]
[TD="align: right"]17[/TD]
[TD]O2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]421C17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]556[/TD]
[TD]A[/TD]
[TD="align: right"]13[/TD]
[TD]N20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]556A13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]556[/TD]
[TD]B[/TD]
[TD="align: right"]13[/TD]
[TD]H2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]556B13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]556[/TD]
[TD]C[/TD]
[TD="align: right"]13[/TD]
[TD]CO2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]556C13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 12"]Therefore the result should find the corresponding gas name for the Line # and replace it looking like this:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID
[/TD]
[TD]Type[/TD]
[TD]H2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LINE13[/TD]
[TD]LINE17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]421[/TD]
[TD]B[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]421B13[/TD]
[TD]421B17[/TD]
[TD][/TD]
[TD]row 18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]421[/TD]
[TD]B[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]124[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]the H2 header above is obtained by[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]the 421B13 above is derived by[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=OFFSET($I$6, MATCH($M$18,$I$7:$I$14,0),-5)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=A18&B18&RIGHT(M17,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]