MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

Returning Multiple values from A Vlookup

• Follow

```I need a formula to return multiple values from a look up table as the number
appears multiple times in a column.
Reference cell is \$C\$6 eg = 4101

Current formula only return the first one it finds
=IF(ISERROR(VLOOKUP(\$C\$6,TNI,3,FALSE)),"",(VLOOKUP(\$C\$6,TNI,3,FALSE)))

I need the Cell to return all the values it finds from the one code eg  =
QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW

Lookup table is TNI (A1:C1053) I need it to return all values in Column (C)
A	              	B			        C
4077	All suburbs	        QRLE/QRLD
4078	Forest Lake	        QRLD/QLGH
4101	Highgate Hill	        QCBD
4101	South Brisbane	QTNS/QCBD/QBMH/QMRE
4101	West End	                QCBD/QAGW
4102	Dutton Park	        QCBD
4102	Woolloongabba	QTNS/QBMH/QMRE/QBBS
4103	All suburbs	        QTNS

Thanks
Michael

```
 0

```Perhaps consider using Autofilter?

Sharon

"mickn74" wrote:

> I need a formula to return multiple values from a look up table as the number
> appears multiple times in a column.
> Reference cell is \$C\$6 eg = 4101
>
> Current formula only return the first one it finds
> =IF(ISERROR(VLOOKUP(\$C\$6,TNI,3,FALSE)),"",(VLOOKUP(\$C\$6,TNI,3,FALSE)))
>
> I need the Cell to return all the values it finds from the one code eg  =
> QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW
>
> Lookup table is TNI (A1:C1053) I need it to return all values in Column (C)
>    A	              	B			        C
> 4077	All suburbs	        QRLE/QRLD
> 4078	Forest Lake	        QRLD/QLGH
> 4101	Highgate Hill	        QCBD
> 4101	South Brisbane	QTNS/QCBD/QBMH/QMRE
> 4101	West End	                QCBD/QAGW
> 4102	Dutton Park	        QCBD
> 4102	Woolloongabba	QTNS/QBMH/QMRE/QBBS
> 4103	All suburbs	        QTNS
>
> Thanks
> Michael
>
```
 0

```Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=IF(COUNTIF(INDEX(TNI,,1),\$C\$6)<ROW(A1),"",
INDEX(TNI,SMALL(IF(INDEX(TNI,,1)=\$C\$6,
ROW(INDEX(TNI,,1))),ROW(A1)),3))

--
Jacob

"mickn74" wrote:

> I need a formula to return multiple values from a look up table as the number
> appears multiple times in a column.
> Reference cell is \$C\$6 eg = 4101
>
> Current formula only return the first one it finds
> =IF(ISERROR(VLOOKUP(\$C\$6,TNI,3,FALSE)),"",(VLOOKUP(\$C\$6,TNI,3,FALSE)))
>
> I need the Cell to return all the values it finds from the one code eg  =
> QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW
>
> Lookup table is TNI (A1:C1053) I need it to return all values in Column (C)
>    A	              	B			        C
> 4077	All suburbs	        QRLE/QRLD
> 4078	Forest Lake	        QRLD/QLGH
> 4101	Highgate Hill	        QCBD
> 4101	South Brisbane	QTNS/QCBD/QBMH/QMRE
> 4101	West End	                QCBD/QAGW
> 4102	Dutton Park	        QCBD
> 4102	Woolloongabba	QTNS/QBMH/QMRE/QBBS
> 4103	All suburbs	        QTNS
>
> Thanks
> Michael
>
```
 0

```Hi,

You may refer to my solution here -
http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"mickn74" <mickn74@discussions.microsoft.com> wrote in message
news:5824E1AE-1553-4B81-9640-C7B60B6B9C82@microsoft.com...
> I need a formula to return multiple values from a look up table as the
> number
> appears multiple times in a column.
> Reference cell is \$C\$6 eg = 4101
>
> Current formula only return the first one it finds
> =IF(ISERROR(VLOOKUP(\$C\$6,TNI,3,FALSE)),"",(VLOOKUP(\$C\$6,TNI,3,FALSE)))
>
> I need the Cell to return all the values it finds from the one code eg  =
> QCBD, QTNS/QCBD/QBMH/QMRE, QCBD/QAGW
>
> Lookup table is TNI (A1:C1053) I need it to return all values in Column
> (C)
>   A               B         C
> 4077 All suburbs         QRLE/QRLD
> 4078 Forest Lake         QRLD/QLGH
> 4101 Highgate Hill         QCBD
> 4101 South Brisbane QTNS/QCBD/QBMH/QMRE
> 4101 West End                 QCBD/QAGW
> 4102 Dutton Park         QCBD
> 4102 Woolloongabba QTNS/QBMH/QMRE/QBBS
> 4103 All suburbs         QTNS
>
> Thanks
> Michael
>
```
 0

3 Replies
1236 Views

Similiar Articles:

7/22/2012 6:24:39 AM