If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VLOOKUP question
Hello everyone, I have those two colums in my worksheet: 37257.00 peanuts 37257.00 banana 37257.00 coconut 37258.00 gold 37258.00 coal I would excel to sort the data this way: 37257.00 peanuts,banana,coconut 37258.00 gold,coal I am trying to use VLOOKUP along with CONCATENATE, but my problem is that VLOOKUP only returns one value ("peanuts") This is what I am writing something like this: CONCATENATE(VLOOKUP(37257,'(data)'!A,4,TRUE),"," ,VLOOKUP(37257,'(data)'!A,4,TRUE),",",VLOOKUP(37 257,'(data)'!A,4,TRUE)) Is it possible to have VLOOKUP to return peanuts, banana and coconut in the same cell? Many thanks, -- duration ------------------------------------------------------------------------ duration's Profile: http://www.excelforum.com/member.php...o&userid=35846 View this thread: http://www.excelforum.com/showthread...hreadid=560159 |
#2
|
|||
|
|||
VLOOKUP question
Hi, I think you're going to have a problem with trying to do this this way. The trouble starts with the way that Vlookup and Hlookup work: They need the data to be sorted so that each value (peanut or coconut) has a different reference (you have three fruit all represented by the same reference - this is why you always get peanuts as the answer. I'm not sure yet what the answer is, but it probably does not involve the vlookup function Not much help, but at least you know what not to use Cheers -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803 View this thread: http://www.excelforum.com/showthread...hreadid=560159 |
#3
|
|||
|
|||
VLOOKUP question
Try this: Keep your table as it was with peanuts, coconuts and bananas all at 37257, gold and coal at 37258. The table is from a15 to b19 This bit is a bit of a cheat and I expect there are better/prettier ways of doing this colA ColB 37257 =IF(A15=B24,B15,"") &","& IF(A16=B24,B16,"") &","& IF(A17=B24,B17,"") What you should get is 37257 peanuts,bananas,coconuts Cheers Jon -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803 View this thread: http://www.excelforum.com/showthread...hreadid=560159 |
#4
|
|||
|
|||
VLOOKUP question
Hello Jon, Thank you very much for your replies! The issue with this technique is that I have ~5000 cells of data. For each value (ex:37257) there is an adjacent cell containing text (ex: coconut). And sometimes I have 'x' number of integer cells (37257) and containing different text. In the example I have three 37257 and two 37258, but then I have five 37259, seven 37260, two 37261 etc. ! I am not sure if you solution can work in this case -- duration ------------------------------------------------------------------------ duration's Profile: http://www.excelforum.com/member.php...o&userid=35846 View this thread: http://www.excelforum.com/showthread...hreadid=560159 |
#5
|
|||
|
|||
VLOOKUP question
Probably not, but then you didn't mention the other 4998 lines did you... I was thinking perhaps a pivot table... Cheers -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803 View this thread: http://www.excelforum.com/showthread...hreadid=560159 |
#6
|
|||
|
|||
VLOOKUP question
It would be simpler creating a seperate columns for for the first match, 2nd MAtch, third match ... and then concatenating them for your final column EG if yout data is the numbers in column A and the Descriptions in column B If you create a table starting in E9 with F9 being 1, g9 being 2 h9 being 3 etc along to the maximum number of products you have for each number and in E10 being the first product number e11 being the second product nummber etc In cell F10 put =IF(ISERROR(OFFSET($B$1,LARGE((($A$1:$A$6000=$E10) *(ROW($A$1:$A$6000))),F$9)-1,0)),"",OFFSET($B$1,LARGE((($A$1:$A$6000=$E10)*(R OW($A$1:$A$6000))),F$9)-1,0)) and enter as an array ctrl shift enter It can then be copied down and accross to give the matching product (starting with the last) the concatenated string can just be =f10&", "&F11&", "&f13&", " etc Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=560159 |
#7
|
|||
|
|||
VLOOKUP question
Thank you Dav! Would you mind attaching a little excel illustrating your example, if you have a minute? It would be ways much easier for me to understand! -- duration ------------------------------------------------------------------------ duration's Profile: http://www.excelforum.com/member.php...o&userid=35846 View this thread: http://www.excelforum.com/showthread...hreadid=560159 |
#8
|
|||
|
|||
VLOOKUP question
Thank you Dav! Would you mind attaching a little excel illustrating your example, if you have a minute? It would be ways much easier for me to understand! -- duration ------------------------------------------------------------------------ duration's Profile: http://www.excelforum.com/member.php...o&userid=35846 View this thread: http://www.excelforum.com/showthread...hreadid=560159 |
#9
|
|||
|
|||
VLOOKUP question
As requested Regards Dav +-------------------------------------------------------------------+ |Filename: matching instances.zip | |Download: http://www.excelforum.com/attachment.php?postid=5009 | +-------------------------------------------------------------------+ -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=560159 |
#10
|
|||
|
|||
VLOOKUP question
As requested Regards Dav +-------------------------------------------------------------------+ |Filename: matching instances.zip | |Download: http://www.excelforum.com/attachment.php?postid=5009 | +-------------------------------------------------------------------+ -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=560159 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VLookup Question? | PH NEWS | Worksheet Functions | 7 | March 2nd, 2006 12:05 PM |
VLOOKUP question | jspizman | Worksheet Functions | 3 | January 22nd, 2006 09:10 PM |
VLOOKUP question | LB79 | General Discussion | 5 | May 21st, 2005 07:12 PM |
vlookup question | jd | Setting up and Configuration | 1 | September 26th, 2003 06:40 AM |
vlookup question | jd | Links and Linking | 1 | September 26th, 2003 06:40 AM |