|
Returning Multiple Values in a lookup
I am putting in a postcode in Cell C6 = 4000
My formula to look up the postcode in Cell C6 is =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) I want the formula to look up all the postcodes that are 4000 in this Table and return the code QCBD/QBMH, QBMH TNI look up table has the same post code yet differeent valuse next to it eg A B C 4000 Brisbane QCBD/QBMH 4000 Spring Hill QBMH 4004 All suburbs QBMH 4005 All suburbs QBMH At the moment my current formula only returns QCBD/QBMH Is tehre any way that i cna get it to return the whole lot QCBD/QBMH, QBMH as there are 2 areas in the same postcode. IN a seperate cell I will also need to return the different suburbs in the same cell for same post code is this the same Thanks Mick |
Returning Multiple Values in a lookup
without adjusting yr formula I'd think of inserting "QCBD/QBMH, QBMH"
in the 3rd column of TNI lookup table in the row where 1st column equals "4000" On 20 Sty, 07:11, mickn74 wrote: I am putting in a postcode in Cell C6 = 4000 My formula to look up the postcode in Cell C6 is =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) I want the formula to look up all the postcodes that are 4000 in this Table and return the code QCBD/QBMH, QBMH TNI look up table has the same post code yet differeent valuse next to it eg A * * * * * * * * B * * * * * * * * * * * C 4000 * *Brisbane * * * * * * * * QCBD/QBMH 4000 * *Spring Hill * * * * * * *QBMH 4004 * *All suburbs * * * * * * *QBMH 4005 * *All suburbs * * * * * * *QBMH At the moment my current formula only returns QCBD/QBMH Is tehre any way that i cna get it to return the whole lot QCBD/QBMH, QBMH as there are 2 areas in the same postcode. IN a seperate cell I will also need to return the different suburbs in the same cell for same post code is this the same Thanks Mick |
Returning Multiple Values in a lookup
Jarek,
Thanks for the thought, If I do the suggested way I need to change the whole of the Look up table which is not possible as these tables are set standards that change every 12 months, if I make changes like this It will get to messy. It is loooking at Postcodes for an entire country. I dont mind changing the formula if there is a better one that would return the values. Is there a formula that can do this Regards, michael "Jarek Kujawa" wrote: without adjusting yr formula I'd think of inserting "QCBD/QBMH, QBMH" in the 3rd column of TNI lookup table in the row where 1st column equals "4000" On 20 Sty, 07:11, mickn74 wrote: I am putting in a postcode in Cell C6 = 4000 My formula to look up the postcode in Cell C6 is =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) I want the formula to look up all the postcodes that are 4000 in this Table and return the code QCBD/QBMH, QBMH TNI look up table has the same post code yet differeent valuse next to it eg A B C 4000 Brisbane QCBD/QBMH 4000 Spring Hill QBMH 4004 All suburbs QBMH 4005 All suburbs QBMH At the moment my current formula only returns QCBD/QBMH Is tehre any way that i cna get it to return the whole lot QCBD/QBMH, QBMH as there are 2 areas in the same postcode. IN a seperate cell I will also need to return the different suburbs in the same cell for same post code is this the same Thanks Mick |
Returning Multiple Values in a lookup
First value using your own formula:
=IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) Say this is in cell K2 and you want subsequent values to show up below, as far as needed. In K3: =IF(ROWS($K$2:K3)<=COUNTIF(INDEX(TNI,0,1),$C$6),IN DEX(TNI,MATCH(1, (INDEX(TNI,0,1)=$C$6)*(COUNTIF($K$2:K2)=0),0),3)," ") Copy this formula down until you start getting blank cells. HTH Kostis Vezerides On 20 , 08:11, mickn74 wrote: I am putting in a postcode in Cell C6 = 4000 My formula to look up the postcode in Cell C6 is =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) I want the formula to look up all the postcodes that are 4000 in this Table and return the code QCBD/QBMH, QBMH TNI look up table has the same post code yet differeent valuse next to it eg A * * * * * * * * B * * * * * * * * * * * C 4000 * *Brisbane * * * * * * * * QCBD/QBMH 4000 * *Spring Hill * * * * * * *QBMH 4004 * *All suburbs * * * * * * *QBMH 4005 * *All suburbs * * * * * * *QBMH At the moment my current formula only returns QCBD/QBMH Is tehre any way that i cna get it to return the whole lot QCBD/QBMH, QBMH as there are 2 areas in the same postcode. IN a seperate cell I will also need to return the different suburbs in the same cell for same post code is this the same Thanks Mick |
Returning Multiple Values in a lookup
I forgot to say that the formula in K3 must be array-entered, using
key combination Shift+Ctrl+Enter. HTH On 20 Ιαν, 12:43, wrote: First value using your own formula: =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) Say this is in cell K2 and you want subsequent values to show up below, as far as needed. In K3: =IF(ROWS($K$2:K3)<=COUNTIF(INDEX(TNI,0,1),$C$6),IN DEX(TNI,MATCH(1, (INDEX(TNI,0,1)=$C$6)*(COUNTIF($K$2:K2)=0),0),3)," ") Copy this formula down until you start getting blank cells. HTH Kostis Vezerides On 20 Éá*, 08:11, mickn74 wrote: I am putting in a postcode in Cell C6 = 4000 My formula to look up the postcode in Cell C6 is =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) I want the formula to look up all the postcodes that are 4000 in this Table and return the code QCBD/QBMH, QBMH TNI look up table has the same post code yet differeent valuse next to it eg A * * * * * * * * B * * * * * * * * * * * C 4000 * *Brisbane * * * * * * * * QCBD/QBMH 4000 * *Spring Hill * * * * * * *QBMH 4004 * *All suburbs * * * * * * *QBMH 4005 * *All suburbs * * * * * * *QBMH At the moment my current formula only returns QCBD/QBMH Is tehre any way that i cna get it to return the whole lot QCBD/QBMH, QBMH as there are 2 areas in the same postcode. IN a seperate cell I will also need to return the different suburbs in the same cell for same post code is this the same Thanks Mick- Απόκρυψη κειμ*νου σε παράθεση - - Εμφάνιση κειμ*νου σε παράθεση - |
Returning Multiple Values in a lookup
Kostis,
I copied the formula and referenced it to the F17 in where the value comes up yet this does not allow me to accept teh formulas it says I have not enough arguments. The TNI look up table is on another sheet would that make any difference? Any other suggestions Regards, Michael " wrote: First value using your own formula: =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) Say this is in cell K2 and you want subsequent values to show up below, as far as needed. In K3: =IF(ROWS($K$2:K3)<=COUNTIF(INDEX(TNI,0,1),$C$6),IN DEX(TNI,MATCH(1, (INDEX(TNI,0,1)=$C$6)*(COUNTIF($K$2:K2)=0),0),3)," ") Copy this formula down until you start getting blank cells. HTH Kostis Vezerides On 20 Ιαν, 08:11, mickn74 wrote: I am putting in a postcode in Cell C6 = 4000 My formula to look up the postcode in Cell C6 is =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) I want the formula to look up all the postcodes that are 4000 in this Table and return the code QCBD/QBMH, QBMH TNI look up table has the same post code yet differeent valuse next to it eg A B C 4000 Brisbane QCBD/QBMH 4000 Spring Hill QBMH 4004 All suburbs QBMH 4005 All suburbs QBMH At the moment my current formula only returns QCBD/QBMH Is tehre any way that i cna get it to return the whole lot QCBD/QBMH, QBMH as there are 2 areas in the same postcode. IN a seperate cell I will also need to return the different suburbs in the same cell for same post code is this the same Thanks Mick |
Returning Multiple Values in a lookup
Of course, I made a couple of mistakes, having not tested the formula.
This formula is tested and assumes the list starts at F17, hence this formula goes to F18: =IF(ROWS($F$17:F18)<=COUNTIF(INDEX(TNI,0,1),$C$6), INDEX(TNI,MATCH(1, (INDEX(TNI,0,1)=$C$6)*(COUNTIF($F$17:F17,INDEX(TNI ,0,3))=0),0),3),"") This formula will produce only unique values. I.e. QCBD/QBMH willonly appear once. Remember to commit with Shift+Ctrl+Enter On 20 , 13:31, mickn74 wrote: Kostis, I copied the formula and referenced it to the *F17 in where the value comes up yet this does not allow me to accept teh formulas it says I have not enough arguments. The TNI look up table is on another sheet would that make any difference? Any other suggestions Regards, Michael * " wrote: First value using your own formula: =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) Say this is in cell K2 and you want subsequent values to show up below, as far as needed. In K3: =IF(ROWS($K$2:K3)<=COUNTIF(INDEX(TNI,0,1),$C$6),IN DEX(TNI,MATCH(1, (INDEX(TNI,0,1)=$C$6)*(COUNTIF($K$2:K2)=0),0),3)," ") Copy this formula down until you start getting blank cells. HTH Kostis Vezerides On 20 , 08:11, mickn74 wrote: I am putting in a postcode in Cell C6 = 4000 My formula to look up the postcode in Cell C6 is =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) I want the formula to look up all the postcodes that are 4000 in this Table and return the code QCBD/QBMH, QBMH TNI look up table has the same post code yet differeent valuse next to it eg A * * * * * * * * B * * * * * * * * * * * C 4000 * *Brisbane * * * * * * * * QCBD/QBMH 4000 * *Spring Hill * * * * * * *QBMH 4004 * *All suburbs * * * * * * *QBMH 4005 * *All suburbs * * * * * * *QBMH At the moment my current formula only returns QCBD/QBMH Is tehre any way that i cna get it to return the whole lot QCBD/QBMH, QBMH as there are 2 areas in the same postcode. IN a seperate cell I will also need to return the different suburbs in the same cell for same post code is this the same Thanks Mick- - - - |
Returning Multiple Values in a lookup
Kostis,
It is retruning a #NA error I copied it as suggested Am i doing something wrong? Thanks for all your help I hope we can get it soon the waisted hours on this a driving me nuts. Thanks mick " wrote: Of course, I made a couple of mistakes, having not tested the formula. This formula is tested and assumes the list starts at F17, hence this formula goes to F18: =IF(ROWS($F$17:F18)<=COUNTIF(INDEX(TNI,0,1),$C$6), INDEX(TNI,MATCH(1, (INDEX(TNI,0,1)=$C$6)*(COUNTIF($F$17:F17,INDEX(TNI ,0,3))=0),0),3),"") This formula will produce only unique values. I.e. QCBD/QBMH willonly appear once. Remember to commit with Shift+Ctrl+Enter On 20 Ιαν, 13:31, mickn74 wrote: Kostis, I copied the formula and referenced it to the F17 in where the value comes up yet this does not allow me to accept teh formulas it says I have not enough arguments. The TNI look up table is on another sheet would that make any difference? Any other suggestions Regards, Michael " wrote: First value using your own formula: =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) Say this is in cell K2 and you want subsequent values to show up below, as far as needed. In K3: =IF(ROWS($K$2:K3)<=COUNTIF(INDEX(TNI,0,1),$C$6),IN DEX(TNI,MATCH(1, (INDEX(TNI,0,1)=$C$6)*(COUNTIF($K$2:K2)=0),0),3)," ") Copy this formula down until you start getting blank cells. HTH Kostis Vezerides On 20 Ιαν, 08:11, mickn74 wrote: I am putting in a postcode in Cell C6 = 4000 My formula to look up the postcode in Cell C6 is =IF(ISERROR(VLOOKUP($C$6,TNI,3,FALSE)),"",(VLOOKUP ($C$6,TNI,3,FALSE))) I want the formula to look up all the postcodes that are 4000 in this Table and return the code QCBD/QBMH, QBMH TNI look up table has the same post code yet differeent valuse next to it eg A B C 4000 Brisbane QCBD/QBMH 4000 Spring Hill QBMH 4004 All suburbs QBMH 4005 All suburbs QBMH At the moment my current formula only returns QCBD/QBMH Is tehre any way that i cna get it to return the whole lot QCBD/QBMH, QBMH as there are 2 areas in the same postcode. IN a seperate cell I will also need to return the different suburbs in the same cell for same post code is this the same Thanks Mick- Απόκρυψη κειμ*νου σε παράθεση - - Εμφάνιση κειμ*νου σε παράθεση - |
| All times are GMT +1. The time now is 10:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com