# Excel - IF Function Problem - Expert Solution

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
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
Excelchat Expert 09/04/2018 - 06:48
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

## Click here to get your free Excelchat help session

Trusted by people who work at