• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining Vlookup with concatenate

J

JonnyDijksman

New Member
Hey Guys


Again, I am sure this has been covered before but I would like to post my specific question for your perusal.


I want to use vlookup to retrieve data from a separate sheet, and as it stands it works fine for a single column | ref - a1, sheet 2 - Data (strings I need are in columns 6,7 and 8 in sheets 2) At them moment it is standard - vlookup(A1,Data,6,0).


How to I use concatenate (if that is indeed the best way to do this) to retrieve the combined string of cells in all 3 columns without having to concatenate all the columns in the sheets separately.


The strings in the separate columns are different parts of an address so they need to be together.


Regards
 
Good day JonnyDijksman


Pulled this from site search


http://chandoo.org/wp/2010/11/08/vlookup-array-formula/
 
Hey bobhc


Thanks for the link, it is really helpful. Unfortunately in this case I can't get it to work as I am trying to combine strings, not values. Also this formula is in the middle of quite a large formula and I can't get it to create an array when I press ctrl+shift+enter.


=IF(ISERROR(INDEX(Mobiles!$A$2:$A$2231,MATCH("*"&MID(F124,1+FIND(" ",F124),999)&"*",Mobiles!$B$2:$B$2231,0))),IF((ISNUMBER(C124)),VLOOKUP("*0*"&C124,Addresses,6,FALSE),"Nothing Found"),INDEX(Mobiles!$A$2:$A$2231,MATCH("*"&MID(F124,1+FIND(" ",F124),999)&"*",Mobiles!$B$2:$B$2231,0))).


This works but the section 'VLOOKUP("*0*"&C124,Addresses,6,FALSE)' I need the 'column 6' section of vlookup to be 'column 6, 7 and 8 :)


regards
 
Hi Jonny ,


The array will be created in 3 successive columns , if you select the three columns , type in the formula as VLOOKUP("*0*"&C124,Addresses,{6,7,8},FALSE) , and then press CTRL SHIFT ENTER.


The three individual components of the address will be displayed in the three columns.


Narayan
 
JonnyDijksman


The Chandoo link shows how to pull multi values with the vlookup, download the workbook
 
Hi Narayank


I need the values of the 3 columns from my 'Data' sheet to be combined and shown as the combined string in my formula cell though. Is that array setup to vlookup data from 3 columns and put them in 3 columns? I need them concatenated into 1 string.


https://docs.google.com/spreadsheet/ccc?key=0AgOpAwUBu4T_dERQZUU3TEZtcDV3QnVJTEtsSlJlNlE&pli=1#gid=0


If I want to use vlookup in C3 to find the employees number (as reference) and return a concatenated address in C3, from columns 5,6,7 (E:E,F:F,G:G) How could I do that?


Regards
 
Hi Johnny,

Is this what you want? "123 Noob street Winnersville"

Trick is to combine 3 vlookups.

=VLOOKUP(B2,D9:G10,2,0)&" "&VLOOKUP(B2,D9:G10,3,0)&" "&VLOOKUP(B2,D9:G10,4,0)


Maybe there is a simpler formula to do this. Ninja's can you comment?
 
Hi ssuresh


How do I get 3 vlookups to work together, when the vlookup for that section functions as the 'True' for an 'IF' function (if I have multiple vlookups won't it confuse the True False statements for that function)


e.g. IF((ISNUMBER(C124)),VLOOKUP("*0*"&C124,Addresses,6,FALSE),"Nothing Found")


the vlookup is the 'if true' return. The nothing found is the 'if false' return. If I add another two vlookups into the argument it will 'error' as saying too many arguments won't it?


Basically I want this argument to say if c124 has a value in it, vlookup an address, which needs to be concatenated as is stored in 3 consecutive columns, to show in a single cell the product of 3 cells, across 3 columns.


regards
 
Hey guys


I got it working via the long way round. Not very tidy, but it does work :)


IF((ISNUMBER(C128)),CONCATENATE(VLOOKUP("*0*"&C128,Addresses,6,FALSE)," ", VLOOKUP("*0*"&C128,Addresses,7,FALSE)," ", VLOOKUP("*0*"&C128,Addresses,8,FALSE)),"Nothing Found")


If anyone has a suggestion how to compact this section I would greatly appreciate it :)


regards
 
Hi JonnyDijksman,


I think you can use:


Code: 
="B"&MATCH($H$1,$A$1:$A$12,0)&" "&"C"&MATCH($H$1,$A$1:$A$12,0)&" "&"D"&MATCH($H$1,$A$1:$A$12,0)


...with little modification in cell referencing to suit your sheet. see this sheet:


http://dl.dropbox.com/u/60644346/JonnyDijksman.xlsx


Regards,

Faseeh
 
Thanks for the help guys. I am still learning this stuff too :: I managed to get it working. Basically my formula says 'if this person's name is in the mobile list, put the mobile number here, if not, put the address'. Unfortunately it looks like I have just spammed the keyboard for 15minutes, but at least it works, I just wish I had the expertise to simplify it. Have a gander (it does work :) )


=IF(ISERROR(INDEX(Mobiles!$A$2:$A$2231,MATCH("*"&MID(F128,1+FIND(" ",F128),999)&"*",Mobiles!$B$2:$B$2231,0))),IF((ISNUMBER(C128)),CONCATENATE(VLOOKUP("*0*"&C128,Addresses,6,FALSE)," ", VLOOKUP("*0*"&C128,Addresses,7,FALSE)," ", VLOOKUP("*0*"&C128,Addresses,8,FALSE)),"Nothing Found"),INDEX(Mobiles!$A$2:$A$2231,MATCH("*"&MID(F128,1+FIND(" ",F128),999)&"*",Mobiles!$B$2:$B$2231,0)))


Kind regards
 
You must log in or register to reply here.
Back
Top