# Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hello I am trying to merge data are you able to help me? I have one column in a workbook that needs to be transfer to my master list
Solved by S. Q. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 11/10/2018 - 07:11
Hi
Excelchat Expert 11/10/2018 - 07:12
Welcome to excelchat
Excelchat Expert 11/10/2018 - 07:12
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows
Excelchat Expert 11/10/2018 - 07:12
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert 11/10/2018 - 07:12
Could you please share the file?
User 11/10/2018 - 07:13
Yes I will add it for you now it is very large so I will just add the first couple rows
Excelchat Expert 11/10/2018 - 07:13
sure
User 11/10/2018 - 07:15
I want to mark the address in sheet 2 as being support or not in sheet one
Excelchat Expert 11/10/2018 - 07:16
Which ever combinations available in sheet 2 are available in sheet 1 should be marked as being support?
User 11/10/2018 - 07:17
If a person lives at the same address in sheet 2
User 11/10/2018 - 07:17
I need that marked in sheet 1 under Household Support as a yes or a no
Excelchat Expert 11/10/2018 - 07:17
okay
Excelchat Expert 11/10/2018 - 07:20
I have the formula in column N
User 11/10/2018 - 07:20
ok let me try it
Excelchat Expert 11/10/2018 - 07:20
sure
User 11/10/2018 - 07:21
All of the values come up as No in my excel
Excelchat Expert 11/10/2018 - 07:21
Yes, Since none of the addresses are available in sheet2
User 11/10/2018 - 07:22
Excelchat Expert 11/10/2018 - 07:24
I am looking up table in sheet 2 to see if the address in column E & F on sheet 1 is available in available there
Excelchat Expert 11/10/2018 - 07:24
If it is available then yes other wise No
Excelchat Expert 11/10/2018 - 07:25
As an example, Iinserted the address in first row on sheet 2 and the result in first row changed to yes
Excelchat Expert 11/10/2018 - 07:25
First, in the sheet 2, I created a concatenated column in C
Excelchat Expert 11/10/2018 - 07:25
And in D have all the values as Yes
Excelchat Expert 11/10/2018 - 07:26
Then used =IFERROR(VLOOKUP(E2&F2,Sheet2!\$C:\$D,2,0),"No")
Excelchat Expert 11/10/2018 - 07:26
to map them
Excelchat Expert 11/10/2018 - 07:26
If the address in sheet 1 is found in sheet 2 then Yes is returned other wise No
Excelchat Expert 11/10/2018 - 07:27
Let me know if you have any queries
User 11/10/2018 - 07:27
That still doesn't work in my excel
User 11/10/2018 - 07:27
is the problem that my excel is not named sheet 1
Excelchat Expert 11/10/2018 - 07:28
What is the second sheet named?
User 11/10/2018 - 07:28
First sheet is named WARD22andPhoneNumbers
User 11/10/2018 - 07:29
the second sheet is Sheet2
Excelchat Expert 11/10/2018 - 07:29
Also, are the columns same?
Excelchat Expert 11/10/2018 - 07:29
I made some modification to sheet2, did you replicate that
User 11/10/2018 - 07:30
yes
Excelchat Expert 11/10/2018 - 07:30
okay
Excelchat Expert 11/10/2018 - 07:31
Can you share screenshots of top few rows in each sheet?
User 11/10/2018 - 07:31
yes shure
User 11/10/2018 - 07:31
sure
User 11/10/2018 - 07:33
User 11/10/2018 - 07:33
User 11/10/2018 - 07:33
Can you see those alright
Excelchat Expert 11/10/2018 - 07:34
yes
User 11/10/2018 - 07:34
Great can you see the issue
Excelchat Expert 11/10/2018 - 07:35
not yet
Excelchat Expert 11/10/2018 - 07:35
In sheet 2, create column C and D
Excelchat Expert 11/10/2018 - 07:35
in c2, enter the formula: =A2&B2
Excelchat Expert 11/10/2018 - 07:36
And in D2 enter Yes
User 11/10/2018 - 07:37
Ok let me try
User 11/10/2018 - 07:38
I got column C created now what do I do for D
Excelchat Expert 11/10/2018 - 07:39
Just enter Yes thorughout
Excelchat Expert 11/10/2018 - 07:39
Then on N2 in sheet 1, enter =IFERROR(VLOOKUP(E2&F2,Sheet2!\$C:\$D,2,0),"No")
User 11/10/2018 - 07:39
Oh ok I see what you mean now
Excelchat Expert 11/10/2018 - 07:41
Did that work?
User 11/10/2018 - 07:41
User 11/10/2018 - 07:42
My table looks like that now I could fit support in D can you change the formula to accommodate that please
Excelchat Expert 11/10/2018 - 07:42
you inserted in column F, insert it in C
Excelchat Expert 11/10/2018 - 07:42
I will modify the formula to not need that at all
User 11/10/2018 - 07:43
Ok
Excelchat Expert 11/10/2018 - 07:43
=IF(IFERROR(VLOOKUP(E2&F2,Sheet2!\$C:\$D,1,0),"No")="No","No","Yes")
Excelchat Expert 11/10/2018 - 07:43
use this formula in N2
User 11/10/2018 - 07:46
Ok I did that but it didn't seem to get as many names as it should
Excelchat Expert 11/10/2018 - 07:48
i am not sure why that could be happening
Excelchat Expert 11/10/2018 - 07:48
The data might be like that
User 11/10/2018 - 07:50
Is there a way I can calculate how many values are in a colume so I can check
Excelchat Expert 11/10/2018 - 07:51
which column do you want to count?
User 11/10/2018 - 07:52
Sheet 1N
User 11/10/2018 - 07:53
If you give me a formula to do that though for any colume I can check my data to see if they match up
Excelchat Expert 11/10/2018 - 07:53
=countif(N2:N29,"Yes")
Excelchat Expert 11/10/2018 - 07:53
This is to count yes
Excelchat Expert 11/10/2018 - 07:53
=countif(N2:N29,"No") ,to count no
User 11/10/2018 - 07:55
Where to I enter this
Excelchat Expert 11/10/2018 - 07:55
any blank cell
Excelchat Expert 11/10/2018 - 07:56
=countif(N:N,"Yes")
Excelchat Expert 11/10/2018 - 07:56
use this one
User 11/10/2018 - 07:57
I have 3470 support
User 11/10/2018 - 07:57
will that formula it tells me I get 1126
User 11/10/2018 - 07:57
with that
Excelchat Expert 11/10/2018 - 07:58
okay
Excelchat Expert 11/10/2018 - 07:59
Can you identify an example, where it shold say support butis not?
User 11/10/2018 - 07:59
Let me check
Excelchat Expert 11/10/2018 - 08:04
Anything
Excelchat Expert 11/10/2018 - 08:04
?
User 11/10/2018 - 08:05
BAKEWELL CRES 301
User 11/10/2018 - 08:05
To show you list I will have to upload a large file is that ok ?
Excelchat Expert 11/10/2018 - 08:05
yes
User 11/10/2018 - 08:05
User 11/10/2018 - 08:07
it will take some time to upload
Excelchat Expert 11/10/2018 - 08:07
okay
Excelchat Expert 11/10/2018 - 08:07
The session will end in 4 minutes
User 11/10/2018 - 08:08
It just upload the first hlaf
User 11/10/2018 - 08:10
There you do it is all there now
User 11/10/2018 - 08:10
Can you please check it for me
Excelchat Expert 11/10/2018 - 08:11
which row?
User 11/10/2018 - 08:11
BAKEWELL CRES 301
User 11/10/2018 - 08:11
It should be household support

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.