Batch macro required for searching value

[Meghnagupta]

Hi,

I have a file name Detail data.xlsx from which I have to pick 'Dep of reptg mnth' column and search that in

hierarchy.xlsx file when I get that 'Dep of reptg mnth' in that column, I have to search vertically in data till P001 according to our 3rd file which is SBU file.

What ever SBU ID we have in this file according to hierarchy file we have pick it's SBU name 

How can we do this?

Example

I pick '91205' from Detail data file

then i will search that in hierarchy file

in hierarchy file you will see 5 hierarchy ID (Column B to columnF), we have to check all the 5 ID till P001 in SBU file

so, we will see '43334' in SBU file attached then we have to pick SBU Name in front of that, like in this case it's 'OO'

Can someone help me create this macro?

I have attached files for the same 

[Meghnagupta]

@binu_acs  @Kottapally_Ramesh @jrlindem any heads up on this?

[binu_acs]

@Meghnaguptaquick question, is the 'Dep of reptg mnth' be checked against all the columns in the hierarchy file or only against the first column (A)? 

[binu_acs]

@Meghnagupta , workflow attached, let me know if you are looking for a different result