# Excel - IF Function Problem - Expert Solution

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

I have two columns with ID numbers representing students in a school- if either column has a number that starts with 480, I need that number in a new column.
Solved by V. D. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 15/05/2018 - 01:16
data analysis
Excelchat Expert 15/05/2018 - 01:16
welcome !
User 15/05/2018 - 01:16
Hi
Excelchat Expert 15/05/2018 - 01:17
You have a question on matching values in a column?
User 15/05/2018 - 01:17
yes. I have over 3K data points each representing a student. Some ID numbers came over correctly in column A and some correctly in Column B
User 15/05/2018 - 01:18
I want to only grab the ID numbers from A and B that start with 480..... can I do that easily?
Excelchat Expert 15/05/2018 - 01:18
I think we can figure out a way that is not too complicated.
Excelchat Expert 15/05/2018 - 01:19
can you upload the sheet or a reasonable sample, maybe first 100 rows if each column.
User 15/05/2018 - 01:19
So I cant upload the exact data because of confidentiality but I can give an example....
Excelchat Expert 15/05/2018 - 01:20
Sure , just so we can have a valid test that the function is right for your actual need.
User 15/05/2018 - 01:23
so here is a sample
User 15/05/2018 - 01:24
so if any number in A or B start with 480, I want it to become C....
Excelchat Expert 15/05/2018 - 01:24
i will load off-line, this viewer is hard to get functions in.
Excelchat Expert 15/05/2018 - 01:27
These are labels entered as numbers - is that always the case?
User 15/05/2018 - 01:27
I will enter them as plain text since that's easier for VLookup which is the next step in my process
User 15/05/2018 - 01:28
is that your question?
Excelchat Expert 15/05/2018 - 01:29
Not pertaining to VLOOKUP, but just to match I need to have consistent. Text is fine.
Excelchat Expert 15/05/2018 - 01:29
Also , there is a chance both rows can have 480?
Excelchat Expert 15/05/2018 - 01:30
as the 1st example?
User 15/05/2018 - 01:30
yes
User 15/05/2018 - 01:30
and in that case they will match
Excelchat Expert 15/05/2018 - 01:31
What I mean is if both A1 and b1 match 480, what goes in C1?
User 15/05/2018 - 01:32
they will be identical, so it should be the exact number so in this example it would need to be 4804
Excelchat Expert 15/05/2018 - 01:32
I missed something?
Excelchat Expert 15/05/2018 - 01:33
So column B has a set of data and Column a, you need to just make sure column A starts with 480?
Excelchat Expert 15/05/2018 - 01:34
Am i looking for 480 in either column or checking row by row for a match?
User 15/05/2018 - 01:35
row by row for each 480 number
User 15/05/2018 - 01:36
if A and B both have a 480 number it will be the same 480 number
Excelchat Expert 15/05/2018 - 01:36
So if A and B have a 480 number , OK to grab A
User 15/05/2018 - 01:37
yes
Excelchat Expert 15/05/2018 - 01:37
ok
Excelchat Expert 15/05/2018 - 01:41
Take a look , I think this is all you need
[Uploaded an Excel file]
User 15/05/2018 - 01:42
SO the 999s I need them to be the 480 numbers in B...
User 15/05/2018 - 01:42
how do I do that?
User 15/05/2018 - 01:43
would I have to do separate and then merge somehow?
Excelchat Expert 15/05/2018 - 01:43
the 999 are NON match B to A.
Excelchat Expert 15/05/2018 - 01:44
so IF not a match get B?
Excelchat Expert 15/05/2018 - 01:44
If B is 480?
User 15/05/2018 - 01:44
if not a match get the 480 number
User 15/05/2018 - 01:44
either from A or B
User 15/05/2018 - 01:44
I need a column of only the 480 numbers from A or B...
User 15/05/2018 - 01:44
Its complicated I know :(
Excelchat Expert 15/05/2018 - 01:45
The best we can do without VBA, is a result that you can sort, hence the 999.
User 15/05/2018 - 01:45
ok I can do that and then copy and paste over...
Excelchat Expert 15/05/2018 - 01:45
So , we need and OR for either column =480
Excelchat Expert 15/05/2018 - 01:46
yes
User 15/05/2018 - 01:46
ok I will try that
User 15/05/2018 - 01:46
its easier than copy pasting 3500 rows after sorting
Excelchat Expert 15/05/2018 - 01:47
so , Either Column and if no match in either column then 999
Excelchat Expert 15/05/2018 - 01:47
is that the right logic?
User 15/05/2018 - 01:47
yes
User 15/05/2018 - 01:47
I can work it from here- so that will help!
User 15/05/2018 - 01:48
Thank you!
Excelchat Expert 15/05/2018 - 01:49
You just need to extend the formula for column B, with an IF (OR(....both conditions, "999" is the then if neither are 480 condition.
User 15/05/2018 - 01:49
=IF(LEFT(TRIM(A1),3)="480",A1,"999")
User 15/05/2018 - 01:49
can you write it sp I can copy and paste it?
Excelchat Expert 15/05/2018 - 01:49
Then copy, paste values, then sort and drop the 999.
Excelchat Expert 15/05/2018 - 01:50
Ok, I need to go back into EXCEL
Excelchat Expert 15/05/2018 - 01:54
Not the right logic , NOT OR. just nested IFs.
Excelchat Expert 15/05/2018 - 01:54
Trying again.
User 15/05/2018 - 01:56
no worries I think I got it worked out on my end now
Excelchat Expert 15/05/2018 - 01:56
=IF(LEFT(TRIM(A1),3)="480", a1, LEFT(TRIM(B1),3)="480"),b1,"999"))
Excelchat Expert 15/05/2018 - 01:57
This is the formula, but needs testing
Excelchat Expert 15/05/2018 - 01:57
If No match in A , look in B, if no match in either "999"
Excelchat Expert 15/05/2018 - 01:58
if this is a 1 time thing, you can put the simple formula in separate coulmns to test A and B, then paste together in another column, since you will have to paste anyway you go.
User 15/05/2018 - 02:01
okay! Thank you so much!
Excelchat Expert 15/05/2018 - 02:01
Ok; thanks for using the service. Please stop by again.

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