VLOOKUP question

[duration]

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 i
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(37257,'(data)'!A,4,TRUE))

Is it possible to have VLOOKUP to return peanuts, banana and coconut i
the same cell?

Many thanks

 

[Jon Quixley]

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 nee
the data to be sorted so that each value (peanut or coconut) has
different reference (you have three fruit all represented by the sam
reference - this is why you always get peanuts as the answer. I'm no
sure yet what the answer is, but it probably does not involve th
vlookup function

Not much help, but at least you know what not to use

Cheer

 

[Jon Quixley]

Try this:

Keep your table as it was with peanuts, coconuts and bananas all a
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/prettie
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
Jo

 

[duration]

Hello Jon,

Thank you very much for your replies!
The issue with this technique is that I have ~5000 cells of data. Fo
each value (ex:37257) there is an adjacent cell containing text (ex
coconut). And sometimes I have 'x' number of integer cells (37257) an
containing different text. In the example I have three 37257 and tw
37258, but then I have five 37259, seven 37260, two 37261 etc. !

I am not sure if you solution can work in this case :confused

 

[Jon Quixley]

Probably not, but then you didn't mention the other 4998 lines di
you...

I was thinking perhaps a pivot table...

Cheer

 

[Dav]

It would be simpler creating a seperate columns for for the first match
2nd MAtch, third match ... and then concatenating them for your fina
column


EG if yout data is the numbers in column A and the Descriptions i
column B

If you create a table starting in E9 with F9 being 1, g9 being 2 h
being 3 etc along to the maximum number of products you have for eac
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)*(ROW($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 produc
(starting with the last)

the concatenated string can just be =f10&", "&F11&", "&f13&", " etc

Regards

Da

 

[duration]

Thank you Dav!
Would you mind attaching a little excel illustrating your example, i
you have a minute? It would be ways much easier for me to understand

 

[duration]

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!

 

[Dav]

As requested

Regards

Da

+-------------------------------------------------------------------
|Filename: matching instances.zip
|Download: http://www.excelforum.com/attachment.php?postid=5009
+-------------------------------------------------------------------

 

[Dav]

As requested

Regards

Dav


+-------------------------------------------------------------------+
|Filename: matching instances.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5009 |
+-------------------------------------------------------------------+