# Excel - IF Function Problem - Expert Solution

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

My criteria is â€ If you find same value from A Column then get the data beside them and paste as transpose to C Column if it is blank otherwise go to D column and paste" thank you guys!
Solved by F. B. in 26 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 04/03/2018 - 02:46
hello
Excelchat Expert 04/03/2018 - 02:46
Hello, are you able to provide a sample data that we can work on?
User 04/03/2018 - 02:46
well...lets say this is my excel
Excelchat Expert 04/03/2018 - 02:47
Please use the Document Preview to the right.
User 04/03/2018 - 02:47
and i would like to do something like that
User 04/03/2018 - 02:48
My criteria is ” If you find same value from A Column then get the data beside them and paste as transpose to C Column if it is blank otherwise go to D column and paste"
Excelchat Expert 04/03/2018 - 02:48
Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
Excelchat Expert 04/03/2018 - 02:48
Okay, one moment while I analyze your data.
User 04/03/2018 - 02:49
thank you sir
Excelchat Expert 04/03/2018 - 02:49
Is there a maximum number of data for each record?
User 04/03/2018 - 02:50
what do you mean?
Excelchat Expert 04/03/2018 - 02:50
Can M3295 appear more than n times?
User 04/03/2018 - 02:50
just 1 time
Excelchat Expert 04/03/2018 - 02:51
In your example, it appeared 4 times though.
User 04/03/2018 - 02:51
from the 4 and 16 column is the results
User 04/03/2018 - 02:51
would like to get
Excelchat Expert 04/03/2018 - 02:51
Yes, I get the result, what I want to understand is the source data.
Excelchat Expert 04/03/2018 - 02:51
Like is it possible for M3295 to appear 300 times in the source data?
User 04/03/2018 - 02:51
yes maybe
User 04/03/2018 - 02:52
im working for suspensions
User 04/03/2018 - 02:52
so there is multiple columns with the same data
User 04/03/2018 - 02:52
matches for different car
Excelchat Expert 04/03/2018 - 02:53
Okay, the solution for this one will require a very slow formula, which means that if it is possible that a record may appear that much then it will slow down the file a lot.
Excelchat Expert 04/03/2018 - 02:53
A VBA/Macro solution may be best but unfortunately we currently do not support VBA/Macro solutions.
Excelchat Expert 04/03/2018 - 02:54
So I'll be giving you the slow formula but you will have to adapt it to work for your file.
User 04/03/2018 - 02:54
thank you very much sir
Excelchat Expert 04/03/2018 - 02:57
I'm working locally. Please give me a few minutes.
User 04/03/2018 - 02:57
its ok
User 04/03/2018 - 02:57
Excelchat Expert 04/03/2018 - 03:02
Excelchat Expert 04/03/2018 - 03:02
Then go to Sheet2.
Excelchat Expert 04/03/2018 - 03:02
It will have a list of the column A number and formulas in the columns next to it.
User 04/03/2018 - 03:03
how?
User 04/03/2018 - 03:03
i mean
User 04/03/2018 - 03:03
it looks fine
User 04/03/2018 - 03:03
ut
Excelchat Expert 04/03/2018 - 03:03
The formulas will then automatically grab whatever is equal to the number to the left.
User 04/03/2018 - 03:03
if i have 20000 rows
User 04/03/2018 - 03:03
it will work?
Excelchat Expert 04/03/2018 - 03:03
It will work but it will be extremely slow.
Excelchat Expert 04/03/2018 - 03:04
The formula is an array formula and Excel does not handle array formulas very well.
Excelchat Expert 04/03/2018 - 03:04
To make it work for 20000 rows you will just have to adjust the formula
Excelchat Expert 04/03/2018 - 03:04
Change Sheet1!\$A\$2:\$A\$8 to Sheet1!\$A\$2:\$A\$20000
Excelchat Expert 04/03/2018 - 03:05
Just make sure the range matches the number of records.
Excelchat Expert 04/03/2018 - 03:05
But like what I said, it will be very slow.
Excelchat Expert 04/03/2018 - 03:05
Of course the other cell references must be changed to fit your data set.
User 04/03/2018 - 03:05
hm
Excelchat Expert 04/03/2018 - 03:05
Like Sheet1!\$B:\$B should be where your output should come from.
User 04/03/2018 - 03:06
how this works?
User 04/03/2018 - 03:06
=IFERROR(INDEX(Sheet1!\$B:\$B;SMALL(IF(Sheet1!\$A\$2:\$A\$8=Sheet2!\$A4;ROW(Sheet1!\$A\$2:\$A\$8));COLUMN()-1));"")
User 04/03/2018 - 03:06
i mean
Excelchat Expert 04/03/2018 - 03:06
I got to be honest, if you are a beginner in Excel this can be very daunting.
User 04/03/2018 - 03:06
i have the =iferror
User 04/03/2018 - 03:06
im not profetional
User 04/03/2018 - 03:06
working for company with suspension and im trying to make description for them
Excelchat Expert 04/03/2018 - 03:06
It's an advanced Excel formula and I won't feel bad if you won't understand it. But the gist of it is that it finds the nth match.
User 04/03/2018 - 03:06
and match the data
Excelchat Expert 04/03/2018 - 03:07
Even Advanced Excel users will have difficult time understanding that formula as array formulas are one of the most , if not the most confusing part of Excel.
Excelchat Expert 04/03/2018 - 03:08
Basically, it checks the value in column A, if it matches then it returns column B.
Excelchat Expert 04/03/2018 - 03:08
Then to the right, it returns the next match and so on.
User 04/03/2018 - 03:08
oh thank you very much sir
Excelchat Expert 04/03/2018 - 03:08
I'm afraid there is no easy way to explain this formula.
Excelchat Expert 04/03/2018 - 03:09
User 04/03/2018 - 03:09
maybe :P but its ok...that was very helpful
User 04/03/2018 - 03:09
but the best way to do these things
User 04/03/2018 - 03:09
is to work with excel vba?
Excelchat Expert 04/03/2018 - 03:09
Best way is to use VBA/Macro, yes.
Excelchat Expert 04/03/2018 - 03:09
The development time would be longer but it would run a lot faster.
User 04/03/2018 - 03:10
User 04/03/2018 - 03:10
learn *
Excelchat Expert 04/03/2018 - 03:10
I'll recommend searching for information via the web or contacting us again in a few days as we may offer VBA/Macro solutions then.
User 04/03/2018 - 03:11
User 04/03/2018 - 03:11
apreciate that !
Excelchat Expert 04/03/2018 - 03:11
Right now that option is disabled for us so we can't help with VBA solutions yet.
User 04/03/2018 - 03:11
that was my first try with this website
Excelchat Expert 04/03/2018 - 03:11
Thank you for understanding.
Excelchat Expert 04/03/2018 - 03:11
Would there be anything else that I can help you with regards to the original question?
User 04/03/2018 - 03:11
no,im good thank you again
Excelchat Expert 04/03/2018 - 03:11
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating if you think I deserve it. :)
Excelchat Expert 04/03/2018 - 03:11
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of. :)
Excelchat Expert 04/03/2018 - 03:11
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User 04/03/2018 - 03:11
ofc!
User 04/03/2018 - 03:12
cya
Excelchat Expert 04/03/2018 - 03:12
Thank you for contacting Got It Pro. Have an awesome day!
User 04/03/2018 - 03:12
how can i rate you?
User 04/03/2018 - 03:12
i have to end session first?
Excelchat Expert 04/03/2018 - 03:12
Yes.
User 04/03/2018 - 03:12
okay,have a good one!
Excelchat Expert 04/03/2018 - 03:12
After ending the session you'll be given the option to. Thank you. That'd help me a lot.
User 04/03/2018 - 03:13
okay thank you sir

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.