Locate cell in database and return row/column header

[vanduima]

Hey everyone,

I have been working on this problem for a while and cannot find an easy solution to do it without VB. If possible, I'd like to use a formula for it. I will accept VB answers though if you have them.

I have a database that is about 600x600. I already have a function that finds the top 3 values of the range. Now I need to find the row header and column header for each of the top three values.

For example, for the table below, I know the top three values are 4, 3, and 2. I want to find a function that will return "Red-Hot" given 4. Any thoughts?
<table border="0" cellpadding="0" cellspacing="0" width="256"><colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td style="width:48pt" width="64">Red</td> <td style="width:48pt" width="64">Blue</td> <td style="width:48pt" width="64">Green</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Hot</td> <td align="right">4
</td> <td align="right">1</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Warm</td> <td align="right">0</td> <td align="right">0</td> <td align="right">2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Cold</td> <td align="right">3</td> <td align="right">0</td> <td align="right">0</td> </tr> </tbody></table>
Thanks for any help you can give.

 

[Haseeb Avarakkan]

Hello,

Assume Row 1 is your Col_Headings & A:A is the Row_Headers. Try this.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Red</td><td style="font-weight: bold;;">Blue</td><td style="font-weight: bold;;">Green</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Top 3</td><td style="font-weight: bold;;">Col  - Row</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Hot</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style=";">Red - Hot</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;;">Warm</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style=";">Red - Cold</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;;">Cold</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style=";">Green - Warm</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=LARGE(<font color="Blue">B$2:D$600,ROWS(<font color="Red">F$2:F2</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">{=INDEX(<font color="Blue">$1:$1,SMALL(<font color="Red">IF(<font color="Green">B$2:D$600=F2,COLUMN(<font color="Purple">B$2:D$600</font>)</font>),COUNTIF(<font color="Green">F$2:F2,F2</font>)</font>)</font>)&" - "&INDEX(<font color="Blue">A:A,SMALL(<font color="Red">IF(<font color="Green">B$2:D$600=F2,ROW(<font color="Purple">B$2:D$600</font>)</font>),COUNTIF(<font color="Green">F$2:F2,F2</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Copy down.

Note: G2 is the Array Formula. Must hit CONTROL+SHIFT+ENTER, not just Enter.

 

[vanduima]

Haseeb,

Thanks for your help. I put that formula in and it works great except for one issue which I didn't explain better before.

I have an additional row/column with a TRUE/FALSE statement based on my specifications so it looks like this below.

Excel Workbook
	A	B	C	D	E
1			TRUE	TRUE	FALSE
2			Red	Blue	Green
3	TRUE	Hot	4	1	0
4	FALSE	Warm	0	0	2
5	TRUE	Cold	3	0	0
Sheet3

Excel 2007

I want to do the exact some thing but only for those rows and columns marked TRUE. I know this is possible with some array formulas and multiplying statements because I did it for my LARGE function. I just can't figure out how to incorporate it here. Thanks for any help you can give!

 

[Haseeb Avarakkan]

Try,

G3,

=IFERROR(LARGE(IF($A$3:$A$600+0,IF($C$1:$E$1+0,$C$3:$E$600)),ROWS(G$3:G3)),"")

H3,

=IFERROR(INDEX($2:$2,SMALL(IF(C$3:E$601=G3,IF($A$3:$A$60+0,IF($C$1:$E$1+0,COLUMN(C$3:E$601)))),COUNTIF(G$3:G3,G3)))&" - "&INDEX(B:B,SMALL(IF(C$3:E$601=G3,IF($A$3:$A$60+0,IF($C$1:$E$1+0,ROW(C$3:E$601)))),COUNTIF(G$3:G3,G3))),"")

Both are confirmed with Control+Shift+Enter.

Copy down.

 

[vanduima]

Haseeb, thanks for your help. That worked perfectly.