Returning multiple HLOOKUP values ignoring blanks?

[alexx579]

Hi all, In A2:A32, I have a list of Toyota vehicle names. In subsequent columns from B:S, I have model years. Each vehicle name does not have the same quantity of model years, some have only 1 other have 11.

My question is this: A2 (Camry) has 4 model years (B2: E2) to the right of it. In Z2:Z5 I have camry autofilled 4 times (corresponding to the number of model years listed). The list continues vertically for each vehicle name. How do I convert their corresponding model years (from B:S) and drop them into Column AA so now they tie out all vertically?

Any formula help is appreciated, don't want to mess with VBA.......

A B C D E

Camry 2012 2014 2004 2008
Scion 2005 2001 1999

Converted to:
Camry 2004
Camry 2008
Camry 2012
Camry 2014
Scion 1999
Scion 2001
Scion 2005
Camry 2014

 

[Eric W]

Try in AA2:

=SMALL(OFFSET($B$1:$L$1,MATCH(Z2,$A$2:$A$32,0),0),COUNTIF($Z$2:$Z2,Z2))

 

[markmzz]

Hi!

If I understand what you want, maybe the Array Formula below can helps.

In AA2 and copy down - use Ctrl+Shift+Enter to enter the formula

=IFERROR(INDEX($A$2:$A$32,SMALL(IF($B$2:$S$32 <> "",ROW($A$2:$A$32)-ROW($A$2)+1),ROWS($AA$2:$AA2)))&" "&
MOD(SMALL(IF($B$2:$S$32 <> "",(ROW($A$2:$A$32)-ROW($A$2)+1)*10^6+$B$2:$S$32),ROWS($AA$2:$AA2)),10^6),"")

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	Z	AA	AB
1	Names	Year01	Year02	Year03	Year04	Year05	Year06	Year07	Year08	Year09	Year10	Year11	Year12	Year13	Year14	Year15	Year16	Year17	Year18			List
2	Toyota01	2012	2014	2004	2008																	Toyota01 2004
3	Toyota02	2005	2001	1999																		Toyota01 2008
4	Toyota03	2004	2008	2012	2014																	Toyota01 2012
5	Toyota04	2005	2001	1999																		Toyota01 2014
6	Toyota05	2018	2010	2018	2017	2002																Toyota02 1999
7	Toyota06	2016	2004	2014	2001	2009	2008															Toyota02 2001
8	Toyota07	2001	2005	2018	2016	2018	2006	2010														Toyota02 2005
9	Toyota08	2009																				Toyota03 2004
10	Toyota09	2003	2015	2017	2004	2009	2010	2011	2000	2016	2014	2008										Toyota03 2008
11	Toyota10	2014	2017																			Toyota03 2012
12	Toyota11	2010	2018																			Toyota03 2014
13	Toyota12	2007	2017																			Toyota04 1999
14	Toyota13	2003	2003																			Toyota04 2001
15	Toyota14	2014	2015																			Toyota04 2005
16	Toyota15	2001	2015																			Toyota05 2002
17	Toyota16	2012	2006																			Toyota05 2010
18	Toyota17	2000	2015																			Toyota05 2017
19	Toyota18	2012	2005																			Toyota05 2018
20	Toyota19	2013	2001																			Toyota05 2018
21	Toyota20	2004	2004																			Toyota06 2001
22	Toyota21	2013	2003																			Toyota06 2004
23	Toyota22	2003	2004																			Toyota06 2008
24	Toyota23	2001	2014	2009	2018	2012	2006	2003	2008	2007												Toyota06 2009
25	Toyota24	2017	2014	2004	2012	2001	2003	2015	2005													Toyota06 2014
26	Toyota25	2005	2001	2015	2017	2007	2013	2000														Toyota06 2016
27	Toyota26	2013	2007	2011	2014	2000	2004	2001	2015													Toyota07 2001
28	Toyota27	2001	2013	2005	2007	2010	2018	2011	2006													Toyota07 2005
29	Toyota28	2001	2008																			Toyota07 2006
30	Toyota29	2000	2011																			Toyota07 2010
31	Toyota30	2012	2005																			Toyota07 2016
32	Toyota31	2004	2001	2000	2010	2007	2012	2006	2008	2005	2003	2014	2015	2018								Toyota07 2006
33																						Toyota07 2010
128																						Toyota31 2014
129																						Toyota31 2015
130																						Toyota31 2018
131
****	**********	*******	*******	*******	*******	*******	*******	*******	*******	*******	*******	*******	*******	*******	*******	*******	*******	*******	*******	**	**	***************	**

<tbody>
</tbody>

Markmzz