ExcelBanter - VLOOKUP question

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - VLOOKUP question (https://www.excelbanter.com/excel-worksheet-functions/98667-vlookup-question.html)

duration

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:D,4,TRUE),"," ,VLOOKUP(37257,'(data)'!A:D,4,TRUE),",",VLOOKUP(37 257,'(data)'!A:D,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

Jon Quixley

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

Jon Quixley

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

duration

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 :confused:

--
duration
------------------------------------------------------------------------
duration's Profile: http://www.excelforum.com/member.php...o&userid=35846
View this thread: http://www.excelforum.com/showthread...hreadid=560159

Jon Quixley

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

Dav

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

duration

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

duration

VLOOKUP question

Dav

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

Dav

VLOOKUP question

All times are GMT +1. The time now is 02:22 PM.