Vlookup Ignoring Hidden Values

[Bugas]

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.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Aaron[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Bruce[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Charlie[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Aaron[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Danny[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Aaron[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Aaron[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Freddie[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]Aaron[/TD]
[TD]10 / 25 / 30 / 20[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

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

Excel 2010
	A	B	C	D	E	F	G	H	I	J
5	Danny	10
6	Aaron	30
7	Aaron	20
8	Freddie	30
9
10	Aaron	2	30	20
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!

 

[Bugas]

Thanks! It works perfect

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

Excel 2010

	A	B	C	D	E	F	G	H	I	J
Danny
Aaron
Aaron
Freddie
Aaron

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"][/TD]

</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!

 

[Domenic]

You're very welcome!