excel - Copying Row Info from one sheet to another based on match -

i have excel book has 2 sheets: 1) import 2) pricing rules.

pricing rules sheet

the a column need match on. example values include sta_pnp4, sta_pst.. , others. there potentially around 50 different rows in sheet, , continue grow on time. each row, there pricing values in columns b cf.

import sheet

this sheet has same number of columns, column a filled out. example values include sta_pnp4_001_00, sta_pnp4_007_00, sta_pst_010_00.. , many more.

what need do:

if text in import sheet column a before second "_" matches column identifer in pricing rules sheet column a, copy rest of b cf of pricing rules sheet row import sheet row matched on.

any idea on begin one?

why don't using formulas only?

assuming :

1.) data in import sheet

(col a)

sta_pnp4_007_00

sta_pnp4_001_00

sta_pnp4_001_00

. .

2.) data in pricing rules sheet

(col a) (col b) (colc) (col d) .......

sta_pnp4 1 2 3 .....

sta_pst 4 5 6 .....

sta_asa2 7 8 9 .....

then write formula in b1 cell of import sheet =iferror(vlookup(left(a1,find("",a1,find("",a1)+1)-1),pricingrules!$a$1:$cf$100,2,0),"")

drag down in column b

and column c , d change index num 2 (3 c) , (4 d) , that.