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

vijay vj
vijay vj used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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

Author

Commented:
thanks sir

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial