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.

	A	B
1	Aaron	10
2	Bruce	20
3	Charlie	15
4	Aaron	25
5	Danny	10
6	Aaron	30
7	Aaron	20
8	Freddie	30
9
10	Aaron	10 / 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]

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
5	Danny	10
6	Aaron	30
7	Aaron	20
8	Freddie	30
9
10	Aaron	2	30	20

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

 

[Domenic]

You're very welcome!