**Question description:**

*This user has given permission to use the problem statement for this blog.*

seeking help in itentifying if A1 = A10 the need values in C1 are B1 and B10

Solved by C. W. in 59 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
09/04/2018 - 06:06

hello! How are you?

User
09/04/2018 - 06:06

Hi I am good wbu

Excelchat Expert
09/04/2018 - 06:06

Could you please walk me through your problem? And would appreciate if you could provide a sample data we could work with

User
09/04/2018 - 06:07

sure..

Excelchat Expert
09/04/2018 - 06:07

oka, thanks

Excelchat Expert
09/04/2018 - 06:08

please let me know when you're done and ready to discuss

User
09/04/2018 - 06:09

so what i am looking for is if i match the word apple for column A then the result in Column C should be B2 and B11 ( anthony, Dusky)

User
09/04/2018 - 06:09

ready to discuss now

Excelchat Expert
09/04/2018 - 06:10

got it, are you okay if we use vba for this?

User
09/04/2018 - 06:10

I can if thats the last option

Excelchat Expert
09/04/2018 - 06:11

let me think of other ways to do it without resorting to coding

User
09/04/2018 - 06:11

sure

Excelchat Expert
09/04/2018 - 06:11

i'll get back to you in a while

User
09/04/2018 - 06:11

sure no worries.. would you mind to sharethe VBA coding for the meantime so i can play with it

Excelchat Expert
09/04/2018 - 06:12

sure. i'll download and modify the excel file then send it to you.

Excelchat Expert
09/04/2018 - 06:15

oh no, i can't send macro-enabled worksheets

Excelchat Expert
09/04/2018 - 06:15

can i give you the code instead?

User
09/04/2018 - 06:15

sure please

Excelchat Expert
09/04/2018 - 06:16

here

[Uploaded an Excel file]

Excelchat Expert
09/04/2018 - 06:17

Function MyLookup(LookFor, FindCol As Range, GetCol As Range) As String
For Each c In FindCol
If LookFor = c Then
If MyLookup = "" Then
MyLookup = Cells(c.Row, GetCol.Column)
Else
MyLookup = MyLookup & ", " & Cells(c.Row, GetCol.Column)
End If
End If
Next c
End FunctionFunction MyLookup(LookFor, FindCol As Range, GetCol As Range) As String
For Each c In FindCol
If LookFor = c Then
If MyLookup = "" Then
MyLookup = Cells(c.Row, GetCol.Column)
Else
MyLookup = MyLookup & ", " & Cells(c.Row, GetCol.Column)
End If
End If
Next c
End Function

User
09/04/2018 - 06:18

could you please guide me the steps to follw

Excelchat Expert
09/04/2018 - 06:18

sure.

Excelchat Expert
09/04/2018 - 06:18

kindly open your excel file

Excelchat Expert
09/04/2018 - 06:18

then press ALT+F11

Excelchat Expert
09/04/2018 - 06:18

a vba windows should appear

Excelchat Expert
09/04/2018 - 06:19

then Insert>New Module

Excelchat Expert
09/04/2018 - 06:19

can you follow so far?

User
09/04/2018 - 06:20

Unable to run

Excelchat Expert
09/04/2018 - 06:20

you don't have to run this one

Excelchat Expert
09/04/2018 - 06:21

are you done with copy and paste formula to the vba module screen?

User
09/04/2018 - 06:21

yes i yes i inserted but i comes in a odd manner

Excelchat Expert
09/04/2018 - 06:21

wait, let me provide to you the link

Excelchat Expert
09/04/2018 - 06:21

https://www.ozgrid.com/forum/forum/help-forums/excel-general/120959-vlookup-multiple-values-and-return-multiple-values-in-one-cell-with-comma-seperated

Excelchat Expert
09/04/2018 - 06:22

just look for the code on the comment dated April 7th, 2013, 00:14

Excelchat Expert
09/04/2018 - 06:22

copy and paste to your module screen

User
09/04/2018 - 06:23

i did

Excelchat Expert
09/04/2018 - 06:23

alright

User
09/04/2018 - 06:23

what to do next after copy and paste

Excelchat Expert
09/04/2018 - 06:24

okay, close the vba window

User
09/04/2018 - 06:24

closed

Excelchat Expert
09/04/2018 - 06:24

then use the mylookup() formula on column c

Excelchat Expert
09/04/2018 - 06:24

it works like vlookup

Excelchat Expert
09/04/2018 - 06:24

are you familiar with using vlookup?

User
09/04/2018 - 06:25

yes i am

User
09/04/2018 - 06:25

where would i find my look up option

Excelchat Expert
09/04/2018 - 06:26

just type =mylookup()

Excelchat Expert
09/04/2018 - 06:26

=MyLookup(LookFor, FindCol As Range, GetCol As Range)

Excelchat Expert
09/04/2018 - 06:26

this is the format of the formula

User
09/04/2018 - 06:29

okies

User
09/04/2018 - 06:29

it comes with an error

Excelchat Expert
09/04/2018 - 06:30

okay, let me check again

Excelchat Expert
09/04/2018 - 06:30

i'll be back in a minute or so, okay?

Excelchat Expert
09/04/2018 - 06:31

hi

Excelchat Expert
09/04/2018 - 06:31

i am back

User
09/04/2018 - 06:32

hi

Excelchat Expert
09/04/2018 - 06:32

kindly use this code instead

User
09/04/2018 - 06:32

sure

Excelchat Expert
09/04/2018 - 06:32

https://superuser.com/questions/893506/how-to-lookup-a-value-and-return-multiple-unique-values-remove-duplicates-to-a

User
09/04/2018 - 06:33

cool i have copied this by using alt F11.. whts next

Excelchat Expert
09/04/2018 - 06:34

remove the original code and replace with the code on the link i provided you with

User
09/04/2018 - 06:34

do we just delete the previous code or is there anything else we need to do

Excelchat Expert
09/04/2018 - 06:35

just delete it, yes

User
09/04/2018 - 06:35

done

User
09/04/2018 - 06:35

whats next

Excelchat Expert
09/04/2018 - 06:35

after you paste the code, use this syntax instead mylookup(range,value)

Excelchat Expert
09/04/2018 - 06:36

you can copy what i did in cell E3 on the document preview on the right

User
09/04/2018 - 06:37

sorry still coming up with an error

Excelchat Expert
09/04/2018 - 06:38

hmmm

Excelchat Expert
09/04/2018 - 06:38

what is the name of the error?

Excelchat Expert
09/04/2018 - 06:38

=mylookup(A2:B11,D3)

Excelchat Expert
09/04/2018 - 06:38

did you type this?

User
09/04/2018 - 06:38

Ambiguous name detected

Excelchat Expert
09/04/2018 - 06:39

can you provide me a screenshot please?

Excelchat Expert
09/04/2018 - 06:39

if it still doesn't work, i still have another code you can try

User
09/04/2018 - 06:40

here we go

Excelchat Expert
09/04/2018 - 06:40

[Uploaded an Excel file]

Excelchat Expert
09/04/2018 - 06:41

[Uploaded an Excel file]

Excelchat Expert
09/04/2018 - 06:41

the former two screenshot worked perfectly for me too

User
09/04/2018 - 06:42

unable to copy the formula

Excelchat Expert
09/04/2018 - 06:43

you mean on the one with the screenshot i provided?

User
09/04/2018 - 06:43

yes

Excelchat Expert
09/04/2018 - 06:43

you can copy it from the document preview on the right :)

User
09/04/2018 - 06:45

sorry same error

Excelchat Expert
09/04/2018 - 06:46

how i wish i could send to you the excel file

Excelchat Expert
09/04/2018 - 06:47

for a while, i'll find a way to send it to you

User
09/04/2018 - 06:48

hmm any other way rather than VBA?

Excelchat Expert
09/04/2018 - 06:48

https://drive.google.com/open?id=1-7fV_tkKr9m3H_PXbmZHy0X3-xF7630y

Excelchat Expert
09/04/2018 - 06:48

try to download the file from this link.

Excelchat Expert
09/04/2018 - 06:49

yes, i found one. but it will involve a lot of additional columns

User
09/04/2018 - 06:50

thats fine happy to do that

Excelchat Expert
09/04/2018 - 06:50

okay, please give me a minute

User
09/04/2018 - 06:52

sure

Excelchat Expert
09/04/2018 - 06:56

here you go

[Uploaded an Excel file]

User
09/04/2018 - 06:57

Thanks for sending the file but it appears that its picking up the wrong data.. apple should match with anthony and dusky

Excelchat Expert
09/04/2018 - 06:57

remember what i used in columns E to H is an array formula

Excelchat Expert
09/04/2018 - 06:58

oops, sorry

Excelchat Expert
09/04/2018 - 06:59

kindly highlight E2 to H2 and change the ROW($B$2:$B$11)-2 to ROW($B$2:$B$11)-1

Excelchat Expert
09/04/2018 - 07:00

then after you made the edit on your keyboard press CTRL+SHIFT+ENTER

User
09/04/2018 - 07:00

sorry didnt get that

User
09/04/2018 - 07:00

could you please resend me the file

User
09/04/2018 - 07:00

thanks

Excelchat Expert
09/04/2018 - 07:00

sure

Excelchat Expert
09/04/2018 - 07:00

here

[Uploaded an Excel file]

User
09/04/2018 - 07:02

Thanks for that now any chance we could do it without adding additional rows or colums

Excelchat Expert
09/04/2018 - 07:02

i'm sorry what i have is only the vba code which doesn't seem to work with you

User
09/04/2018 - 07:03

hmm well thanks heaps for your help in the matter

User
09/04/2018 - 07:03

Really appreciate your help

Excelchat Expert
09/04/2018 - 07:03

sure, you are most welcome!

Excelchat Expert
09/04/2018 - 07:03

if you wish to study the solution more you may refer to https://www.ablebits.com/office-addins-blog/2017/02/22/vlookup-multiple-values-excel/

Excelchat Expert
09/04/2018 - 07:04

under Formula 2

Excelchat Expert
09/04/2018 - 07:04

is it okay if I mark this problem solved?

User
09/04/2018 - 07:04

Thanks for youyr help

User
09/04/2018 - 07:04

sure i will

Excelchat Expert
09/04/2018 - 07:05

by the way, if you don't need anything else, please feel free to end the session.

Excelchat Expert
09/04/2018 - 07:05

also, you rating and feedback is greatly appreciated :)

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*