Excel - IF Function Problem - Expert Solution

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

I have a list of data on one sheet, that I want to have copied to another sheet if a particular criteria is met, namely that it matches the client code listed in cell C1. Can you help?
Solved by A. H. in 53 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 16/04/2018 - 12:45
Welcome to GotitPro!
User 16/04/2018 - 12:45
Hi
User 16/04/2018 - 12:45
I see this preview is in Sheets, what I'm trying to do is in excel
User 16/04/2018 - 12:46
I actually got it to work in sheets, but cannot figure it out in excel
User 16/04/2018 - 12:46
the formula is: =QUERY('Harvest Import'!$A$2:$F$435, "select A where (B=C3)")
Excelchat Expert 16/04/2018 - 12:46
Could you please provide me sample data ?
Excelchat Expert 16/04/2018 - 12:47
Function in google sheet and excel desktop are different.
Excelchat Expert 16/04/2018 - 12:47
Query () does not work in excel.
Excelchat Expert 16/04/2018 - 12:49
Please let me know which data you want and on which sheet.
User 16/04/2018 - 12:50
OK, sample data in there. What I'm trying to do is search the data on sheet 1 and copy the data where the info in column B matches the code in Cell C3 on Sheet 3
User 16/04/2018 - 12:50
i guess the Begin and End aren't important for this question, so don't worry about that for now.
User 16/04/2018 - 12:50
I also have a range question as a follow up
Excelchat Expert 16/04/2018 - 12:51
I got your requirement, you want data from sheet one where project code matches, ?right
Excelchat Expert 16/04/2018 - 12:51
right ?
Excelchat Expert 16/04/2018 - 12:52
You want this in excel 2016, right ?
User 16/04/2018 - 12:52
yes
Excelchat Expert 16/04/2018 - 12:53
One more question, as I can see there are more than 1 instances for Code
User 16/04/2018 - 12:53
yes, i want all the lines with that code copied over
Excelchat Expert 16/04/2018 - 12:53
You want to fetch all , right ?
User 16/04/2018 - 12:53
yes
Excelchat Expert 16/04/2018 - 12:54
Thank you for confirmation.
Excelchat Expert 16/04/2018 - 12:54
Please be with me, I am working on it.
User 16/04/2018 - 12:58
Thank you! If you fix this for me, you will be my hero! Then I'll give you a second problem to solve. LOL
Excelchat Expert 16/04/2018 - 12:58
Sure :D
Excelchat Expert 16/04/2018 - 01:05
I am still working.
User 16/04/2018 - 01:06
OK thanks!
Excelchat Expert 16/04/2018 - 01:10
Please check the solution.
Excelchat Expert 16/04/2018 - 01:11
Hi
Excelchat Expert 16/04/2018 - 01:11
Are you there ?
User 16/04/2018 - 01:12
yes
User 16/04/2018 - 01:12
ok that looks right. What's the excel formula?
Excelchat Expert 16/04/2018 - 01:12
=ArrayFormula(INDEX(Sheet1!$A$2:$C$7, SMALL(IF($C$1=Sheet1!$B$2:$B$7, ROW(Sheet1!$B$2:$B$7)-MIN(ROW(Sheet1!$B$2:$B$7))+1, ""), ROW(A1)),COLUMN(A1)))
Excelchat Expert 16/04/2018 - 01:13
This is the formula I have applied, you can see in sheet3
User 16/04/2018 - 01:13
and that will work in excel, not sheets, right?
Excelchat Expert 16/04/2018 - 01:14
Yes
User 16/04/2018 - 01:14
Let me play with it in my actual sheet with the actual names and see if I can make it work. In the meantime, I have question #2
Excelchat Expert 16/04/2018 - 01:15
=INDEX(Sheet1!$A$2:$C$7, SMALL(IF($C$1=Sheet1!$B$2:$B$7, ROW(Sheet1!$B$2:$B$7)-MIN(ROW(Sheet1!$B$2:$B$7))+1, ""), ROW(A1)),COLUMN(A1))
Excelchat Expert 16/04/2018 - 01:15
Use this in excel and press Ctrl+shift+Enter to convert the same into array formula.
User 16/04/2018 - 01:16
If I have several invoices listed on Sheet 3 and i want to copy them to the invoices sheet, I know how to do that... but how do I copy the invoices from all the sheets between begin and end
User 16/04/2018 - 01:16
so use what you typed into chat just now?
Excelchat Expert 16/04/2018 - 01:18
Only one question per session is permitted as per company policy
Excelchat Expert 16/04/2018 - 01:18
Please repost for other query.
User 16/04/2018 - 01:19
OK, can you hang on while I try to get this to work in Excel?
Excelchat Expert 16/04/2018 - 01:20
Sure.
User 16/04/2018 - 01:23
I'm getting a ?Name# error
Excelchat Expert 16/04/2018 - 01:25
Please check the references
Excelchat Expert 16/04/2018 - 01:26
Also press Ctrl+shift+ enter in formula baar
Excelchat Expert 16/04/2018 - 01:27
Are you there ?
Excelchat Expert 16/04/2018 - 01:28
See the sheet3 for example where I have applied the same formula. You columns should be same as in the sheet visible on right pane.
User 16/04/2018 - 01:29
I got it to work, but it's only giving me one row of data, not all the instances
User 16/04/2018 - 01:29
Oh, I have to drag the formula down...
Excelchat Expert 16/04/2018 - 01:29
You will have drag down
Excelchat Expert 16/04/2018 - 01:29
Yes
User 16/04/2018 - 01:30
I dragged down obviously more rows that I have data for and it gives me a #NUM error, any way to make that go away or no?
User 16/04/2018 - 01:30
I'm happy, just curious about that?
Excelchat Expert 16/04/2018 - 01:30
use iferror( formula, ")
Excelchat Expert 16/04/2018 - 01:31
=iferror(INDEX(Sheet1!$A$2:$C$7, SMALL(IF($C$1=Sheet1!$B$2:$B$7, ROW(Sheet1!$B$2:$B$7)-MIN(ROW(Sheet1!$B$2:$B$7))+1, ""), ROW(A1)),COLUMN(A1)),"")
User 16/04/2018 - 01:32
Am I just adding that to the front of the other formula?
Excelchat Expert 16/04/2018 - 01:32
Yes, actually formula within iferror
Excelchat Expert 16/04/2018 - 01:32
As I did above.
User 16/04/2018 - 01:33
Is there a way to unarray it so i can add it to the formula i already modified
Excelchat Expert 16/04/2018 - 01:33
IFERROR( formula, "")
Excelchat Expert 16/04/2018 - 01:33
Can you paste your modified formula here ?
User 16/04/2018 - 01:34
=INDEX(Harvest!$A$2:$I$700, SMALL(IF($D$1=Harvest!$C$2:$C$700, ROW(Harvest!$C$2:$C$700)-MIN(ROW(Harvest!$C$2:$C$700))+1, ""), ROW(A1)),COLUMN(A1))
User 16/04/2018 - 01:34
Oh, never mind...
User 16/04/2018 - 01:34
When I clicked in the formula bar it took the array off
User 16/04/2018 - 01:34
ha
Excelchat Expert 16/04/2018 - 01:34
=IFERROR(INDEX(Harvest!$A$2:$I$700, SMALL(IF($D$1=Harvest!$C$2:$C$700, ROW(Harvest!$C$2:$C$700)-MIN(ROW(Harvest!$C$2:$C$700))+1, ""), ROW(A1)),COLUMN(A1)),"")
Excelchat Expert 16/04/2018 - 01:35
Yeah, it goes on click
User 16/04/2018 - 01:36
Beautiful...
User 16/04/2018 - 01:36
So, what is an array? I've never used that before
Excelchat Expert 16/04/2018 - 01:37
It work on range instead of single cell
Excelchat Expert 16/04/2018 - 01:37
Hope you are satisfied with the solution provided.
User 16/04/2018 - 01:38
Oh, cool.
User 16/04/2018 - 01:38
Yes, thank you!
User 16/04/2018 - 01:38
My hero!
Excelchat Expert 16/04/2018 - 01:38
Please leave your valuable feedback.
User 16/04/2018 - 01:38
Will do! Good night!
Excelchat Expert 16/04/2018 - 01:38
Thank you for choosing gotitPro!
Excelchat Expert 16/04/2018 - 01:38
Good night!

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

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc