Hi,
I have a data structure like heading & subheading. Each heading has four subheadings, I want to lookup a data by matching heading & then subheadings, refer data below:
Data is like below:
I want output in below format:
Solution:
First we need to match heading & get location of heading & after that we can create specific range related to heading for searching & apply Hlookup formula in that ramge to get result.
Formula 1 : =HLOOKUP(B$7,OFFSET($A$1,2,MATCH($A8,$A$1:$AB$1,0)-1,2,4),2,0)
Formula 2: =INDEX($A$1:$AB$4,4,MATCH($A8,$A$1:$AB$1,0))
Formula 1 is though complecated but gives result in case of there is jumble or interchange in sub headings.
Comment or write to us on forum for clarifications or to post your querries www.ExcelVbaLab.com
Cheers!!
Tags: #Excel #Formula #double #lookup, #match #heading & #subheading and #lookup #data #from #main #table #to #Sub #Table
I have a data structure like heading & subheading. Each heading has four subheadings, I want to lookup a data by matching heading & then subheadings, refer data below:
Data is like below:
I want output in below format:
Solution:
First we need to match heading & get location of heading & after that we can create specific range related to heading for searching & apply Hlookup formula in that ramge to get result.
Formula 1 : =HLOOKUP(B$7,OFFSET($A$1,2,MATCH($A8,$A$1:$AB$1,0)-1,2,4),2,0)
Formula 2: =INDEX($A$1:$AB$4,4,MATCH($A8,$A$1:$AB$1,0))
Formula 1 is though complecated but gives result in case of there is jumble or interchange in sub headings.
Comment or write to us on forum for clarifications or to post your querries www.ExcelVbaLab.com
Cheers!!
Tags: #Excel #Formula #double #lookup, #match #heading & #subheading and #lookup #data #from #main #table #to #Sub #Table
No comments:
Post a Comment