how to return set of columns values with one column header name lookup

Dear sir, i have set of values in column A,B and so on each column has header name and their values are below the header name row for (e.g) 1 to 10 rows have values 1 to 10. and below that row another column header name with 1 to 10 values below that row and so on, is it possible to get that set of 1 to 10 values with looking up of column names. i have attached the excel sheet , where sheet 2 has values and in sheet 3 ,i have highlighted what i need to do so, thanks
New-Microsoft-Excel-Worksheet.xlsx
vijay vjAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
I think you are trying to get the ratio of values from two columns on Sheet2 based on splitting the text in Sheet3 cell B5 at the hyphen. If so, you don't need any of the values on Sheet2 below row 41. You could get your desired ratio using a formula like:
=HLOOKUP(LEFT(B$5,FIND("-",B$5)-1),Sheet2!$1:$41,ROWS(B$5:B6)+1,FALSE)/HLOOKUP(MID(B$5,FIND("-",B$5)+1,25),Sheet2!$1:$41,ROWS(B$5:B6)+1,FALSE)

Open in new window

New-Microsoft-Excel-WorksheetQ29011.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vijay vjAuthor Commented:
thanks sir
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.