Lookup in Row, match data in cells of that row and return Column header

[kaushik_birmiwal]

Hi All,

I am facing a peculiar problem. I want to lookup for a text string in the rows, match a particular number in the values of that row, and then return the column header to which it matches.

Below is a sample data:
[TABLE="width: 555"]
<tbody>[TR]
[TD]Address[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD]GHI[/TD]
[TD]JHL[/TD]
[TD]MNO[/TD]
[/TR]
[TR]
[TD]Ayala Alabang Village, Muntinlupa City 1780[/TD]
[TD="align: right"]18.7[/TD]
[TD="align: right"]22.3[/TD]
[TD="align: right"]20.6[/TD]
[TD="align: right"]430[/TD]
[TD="align: right"]6.4[/TD]
[/TR]
[TR]
[TD]Miranda Street, Angeles City 2009[/TD]
[TD="align: right"]52.6[/TD]
[TD="align: right"]58.9[/TD]
[TD="align: right"]64.8[/TD]
[TD="align: right"]494.4[/TD]
[TD="align: right"]75.1[/TD]
[/TR]
[TR]
[TD]East Kamias, Quezon City[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5.4[/TD]
[TD="align: right"]11.4[/TD]
[TD="align: right"]443.9[/TD]
[TD="align: right"]24.6[/TD]
[/TR]
[TR]
[TD]Antipolo City, Rizal Province[/TD]
[TD="align: right"]5.3[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]6.3[/TD]
[TD="align: right"]446.1[/TD]
[TD="align: right"]26.8[/TD]
[/TR]
[TR]
[TD]ML Quezon Street, Antipolo City[/TD]
[TD="align: right"]11.2[/TD]
[TD="align: right"]6.2[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]444.4[/TD]
[TD="align: right"]25.2[/TD]
[/TR]
[TR]
[TD]Hemady Street, New Manila, Quezon City[/TD]
[TD="align: right"]11.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]444.5[/TD]
[TD="align: right"]25.2[/TD]
[/TR]
[TR]
[TD]Rotonda Baclaran, Paranaque City[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]12.9[/TD]
[TD="align: right"]14.7[/TD]
[TD="align: right"]436.7[/TD]
[TD="align: right"]17.4[/TD]
[/TR]
[TR]
[TD]Gatuslao Street, Bacolod City[/TD]
[TD="align: right"]445[/TD]
[TD="align: right"]447.3[/TD]
[TD="align: right"]445.5[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]430.1[/TD]
[/TR]
[TR]
[TD]Bacolod City[/TD]
[TD="align: right"]443.7[/TD]
[TD="align: right"]446[/TD]
[TD="align: right"]444.3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]428.8[/TD]
[/TR]
[TR]
[TD]Molino II, Bacoor Cavite[/TD]
[TD="align: right"]21.3[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]27.2[/TD]
[TD="align: right"]430.8[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
</tbody>[/TABLE]


I have a list which contains all the values of Column A and I need to run a formula such that I can get Column headers for for cells which have value of less than 2.5.

I am sharing a Sample output below:
[TABLE="width: 299"]
<tbody>[TR]
[TD]East Kamias, Quezon City[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]Hemady Street, New Manila, Quezon City[/TD]
[TD]GHI[/TD]
[/TR]
[TR]
[TD]Gatuslao Street, Bacolod City[/TD]
[TD]JHL[/TD]
[/TR]
[TR]
[TD]Bacolod City[/TD]
[TD]MNO[/TD]
[/TR]
</tbody>[/TABLE]


I have tried multiple combinations of Vlookup & Match, Index, Match & Match, Offset, Index & Match, etc. I have also tried to use Cell, Address functions. However, I have not been successful yet.

Regards,
Kaushik

 

[Aladin Akyurek]

Let A:F of Sheet1 house the data.

Sheet2 (the processing)

[TABLE="class: grid, width: 367"]
<tbody>[TR]
[TD="align: right"]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]East Kamias, Quezon City[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]Antipolo City, Rizal Province[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]ML Quezon Street, Antipolo City[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]Hemady Street, New Manila, Quezon City[/TD]
[/TR]
[TR]
[TD]JHL[/TD]
[TD]Gatuslao Street, Bacolod City[/TD]
[/TR]
[TR]
[TD]JHL[/TD]
[TD]Bacolod City[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In A2 of Sheet2 just enter:

=COUNTIFS(Sheet1!B2:F11,"<"&A1)

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",INDEX(Sheet1!$B$1:$F$1,SMALL(IF(ISNUMBER(Sheet1!$B$2:$F$11),IF(Sheet1!$B$2:$F$11 < $A$1,COLUMN(Sheet1!$B$1:$F$1)-COLUMN(Sheet1!$B$1)+1)),ROWS($A$4:A4))))

In B4 control+shift+enter, not just enter, and copy down:

=IF($A4="","",INDEX(Sheet1!$A$2:$A$11,SMALL(IF(ISNUMBER(INDEX(Sheet1!$B$2:$F$11,0,MATCH($A4,Sheet1!$B$1:$F$1,0))),IF(INDEX(Sheet1!$B$2:$F$11,0,MATCH($A4,Sheet1!$B$1:$F$1,0)) < $A$1,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1)),COUNTIFS($A$4:A4,A4))))