Friday, March 25, 2016

Excel Formula double lookup, match heading & sub heading and lookup data from main table

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