Vlookup Ignoring Hidden Values

Bugas

New Member
Joined
Sep 17, 2012
Messages
42
Sometimes my data is hidden and I need to get the values. I don't know how to use SUBTOTAL with a VLOOKUP or IF statement, otherwise it would be easy.

I'm trying to get only the visible scores for different players.


AB
1Aaron10
2Bruce20
3Charlie15
4Aaron25
5Danny10
6Aaron30
7Aaron20
8Freddie30
9
10Aaron10 / 25 / 30 / 20
11

<tbody>
</tbody>


For instance, in the following table for Aaron (A10) I get these results (B10): 10 / 25 / 30 / 20. With a VLOOKUP it would be easy.

However, I need to hide columns 1, 2, 3 and 4. Then for Aaron in this case I would get 30 / 20. For Bruce it would be "".

Without using VBA is there any chance to do it?

Thanks :)
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
Can the results be returned in separate cells? If so, try...

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;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><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Danny</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Aaron</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">30</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Aaron</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Freddie</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">30</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Aaron</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">30</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

B10, confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(SUBTOTAL(103,OFFSET($A$1:$A$8,ROW($A$1:$A$8)-ROW($A$1),0,1))>0,IF($A$1:$A$8=$A10,1)))

C10, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=IF(COLUMNS($C10:C10)<=$B$10,INDEX($B$1:$B$8,SMALL(IF(SUBTOTAL(103,OFFSET($A$1:$A$8,ROW($A$1:$A$8)-ROW($A$1),0,1))>0,IF($A$1:$A$8=$A10,ROW($A$1:$A$8)-ROW($A$1)+1)),COLUMNS($C10:C10))),"")

Note that in addition to manually hidden rows, filtered data will also be reflected in the results.

Hope this helps!
 
Last edited:

Bugas

New Member
Joined
Sep 17, 2012
Messages
42
Thanks! It works perfect :)

Can the results be returned in separate cells? If so, try...

Excel 2010
ABCDEFGHIJ
5Danny10
6Aaron30
7Aaron20
8Freddie30
9
10Aaron23020

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1



B10, confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(SUBTOTAL(103,OFFSET($A$1:$A$8,ROW($A$1:$A$8)-ROW($A$1),0,1))>0,IF($A$1:$A$8=$A10,1)))

C10, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=IF(COLUMNS($C10:C10)<=$B$10,INDEX($B$1:$B$8,SMALL(IF(SUBTOTAL(103,OFFSET($A$1:$A$8,ROW($A$1:$A$8)-ROW($A$1),0,1))>0,IF($A$1:$A$8=$A10,ROW($A$1:$A$8)-ROW($A$1)+1)),COLUMNS($C10:C10))),"")

Note that in addition to manually hidden rows, filtered data will also be reflected in the results.

Hope this helps!
 

Forum statistics

Threads
1,082,589
Messages
5,366,494
Members
400,896
Latest member
Scab

Some videos you may like

This Week's Hot Topics

Top