Returning Multiple values from A Vlookup
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 |
Returning Multiple values from A Vlookup
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 |
Returning Multiple values from A Vlookup
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 |
Returning Multiple values from A Vlookup
Hi,
You may refer to my solution here - http://office.microsoft.com/en-us/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "mickn74" wrote in message ... 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 |
All times are GMT +1. The time now is 11:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com