Excel - IF Function Problem - Expert Solution

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

Hello - I have a giant spreadsheet that I regularly need to go into to find just one row, copy that data, and paste it into another small spreadsheet which I use to do the work I need to do with the data. Is there a way I could do so without having to open the giant spreadsheet and find the right row every time? I can show you the spreadsheets and the process if that helps.
Solved by E. Y. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 16/10/2018 - 03:15
Welcome to excelchat!
Excelchat Expert 16/10/2018 - 03:15
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.
User 16/10/2018 - 03:15
Sure - is there a way to pay to use you more to handle additional questions/problems?
Excelchat Expert 16/10/2018 - 03:16
Unfortunately as it is, you can buy unlimited number of sessions but you can only get help on only one specific problem during a session.
User 16/10/2018 - 03:17
Okay that's fine - I've got a number of things I'd like assistance with so if those goes well I'm eager to do more sessions!
Excelchat Expert 16/10/2018 - 03:17
Great!
Excelchat Expert 16/10/2018 - 03:18
So, about the problem that was described in the description, what do you have in the file and how do you determine which row you need?
Excelchat Expert 16/10/2018 - 03:18
Please also send me the file in question.
User 16/10/2018 - 03:19
So here's the deal - I run a company that helps people fill out paperwork for their boats. I have a HUGE database with information on every boat registered in the US. I'll copy a link here because it's a big file: https://www.dropbox.com/s/r6xfdbpoc8icrxw/Merchant%20Vessels%20Torqued.xlsx?dl=0
User 16/10/2018 - 03:20
I have a second much smaller spreadsheet which I use just for my customers: https://www.dropbox.com/s/e2m332q6klxgds8/The%20Master%20Excel.xlsx?dl=0
User 16/10/2018 - 03:21
So when I get a customer, I look up the customer's boat in the huge spreadsheet, and then copy and paste the customer's boat information from the huge spreadsheet with ALL the boats in the US, into the second spreadsheet, which I then use to draft boat paperwork for my customers.
Excelchat Expert 16/10/2018 - 03:21
I'm downloading the files.
User 16/10/2018 - 03:22
I'm looking for a way that I can streamline the process without having to open up the huge spreadsheet and copy and paste every time. Like, if I could simply put in the customer's name in the small spreadsheet, and then it would automatically pull the relevant data from the huge spreadsheet. Is anything like that possible?
Excelchat Expert 16/10/2018 - 03:22
Meanwhile, how do you lookup a customers boat? With name or customer number or something else?
Excelchat Expert 16/10/2018 - 03:22
Yeah, something like that can be done.
User 16/10/2018 - 03:23
Usually with the customer number which is called "Official Number" in the spreadsheets - that number is unique to each boat so it's the best method. I'd love it if I could simply type in the boat number in the small spreadsheet and then all the appropriate data would magically appear.
Excelchat Expert 16/10/2018 - 03:24
That is very much possible. Please stay with me.
Excelchat Expert 16/10/2018 - 03:25
Which column is official number in the spreadsheets?
User 16/10/2018 - 03:26
It's column K in both spreadsheets
Excelchat Expert 16/10/2018 - 03:29
Alright, I have both files opened in my pc.
User 16/10/2018 - 03:29
Also if we run out of time can I pay to continue?
Excelchat Expert 16/10/2018 - 03:30
You need to copy the whole row from the master file to the 2nd file by matching column K value which you would manually input?
Excelchat Expert 16/10/2018 - 03:30
We can extend for another 20 minutes for free.
User 16/10/2018 - 03:30
Yup - so currently I just highlight the entire row and copy it over from the master file to the second file.
User 16/10/2018 - 03:31
But the solution I'd like would be one where I simply type in the boat number in column K of the smaller file, and it pulls the matching row from the huge file without having to open the huge file.
Excelchat Expert 16/10/2018 - 03:31
You will be asked shortly to extend session. Please select YES.
User 16/10/2018 - 03:32
Thanks - just did so.
Excelchat Expert 16/10/2018 - 03:35
Can you tell me how many rows of data are there in the big file?
User 16/10/2018 - 03:35
368,120
Excelchat Expert 16/10/2018 - 03:36
Thank you. Please stay with me.
User 16/10/2018 - 03:38
Roger, much appreciated!
Excelchat Expert 16/10/2018 - 03:38
The files size is proving to be overwhelming for my pc's processing power, thus taking a bit time. Please stay with me.
User 16/10/2018 - 03:39
Yeah, that's exactly why I want to be able to do this without opening up the spreadsheet every time. What if I sent you just the first 100 rows of the spreadsheet in a smaller file, would that make it easier?
Excelchat Expert 16/10/2018 - 03:40
Just to make sure, even if you don't want to open the 2nd file where you are looking for information, it will take the same amount of time for the formula to return a result.
Excelchat Expert 16/10/2018 - 03:40
I have the formula.
Excelchat Expert 16/10/2018 - 03:41
I have wrote a index/match formula.
User 16/10/2018 - 03:41
Okay, gotcha. Thank you!
Excelchat Expert 16/10/2018 - 03:41
Please put the two files in the same folder on your pc.
User 16/10/2018 - 03:42
Okay great, that's already done.
Excelchat Expert 16/10/2018 - 03:42
This formula requires that you keep the files in the same folder.
Excelchat Expert 16/10/2018 - 03:42
=INDEX('[Merchant Vessels Torqued.xlsx]Merchant Vessels of the United '!A$2:A$368120,MATCH($K2,'[Merchant Vessels Torqued.xlsx]Merchant Vessels of the United '!$K$2:$K$368120,0))
Excelchat Expert 16/10/2018 - 03:43
Now this formula you give you the information of column A from then 2nd file for a official number that is being inputted in the cell K2
Excelchat Expert 16/10/2018 - 03:44
You can test it by placing the formula in A2 and input a official number in K2
User 16/10/2018 - 03:45
Okay great, thank you! Does this only work if the two spreadsheets have identical columns, like in this instance?
User 16/10/2018 - 03:45
Or is there a way to make it work if I have to transpose columns - like if the data in column K of one spreadsheet is in column L in another spreadsheet?
Excelchat Expert 16/10/2018 - 03:45
What do you mean again?
Excelchat Expert 16/10/2018 - 03:46
Oh I see.
Excelchat Expert 16/10/2018 - 03:47
You mean if the file where you need data contains official number in column K but the main database contains the official number in column L, then what?
User 16/10/2018 - 03:47
Exactly.
Excelchat Expert 16/10/2018 - 03:47
See the MATCH($K20,'[Merchant Vessels Torqued.xlsx]Merchant Vessels of the United '!$K$2:$K$368120,0) part of the formula?
Excelchat Expert 16/10/2018 - 03:48
here it means, we are matching the value of K20 with column K of Merchant Vessels Torqued.xlsx workbooks Merchant Vessels of the United worksheets column K.
Excelchat Expert 16/10/2018 - 03:49
This returns a row number, and then =INDEX('[Merchant Vessels Torqued.xlsx]Merchant Vessels of the United '!A$2:A$368120 part of the formula returns a value from the same row numbers column A of Merchant Vessels Torqued.xlsx workbooks Merchant Vessels of the United worksheets
Excelchat Expert 16/10/2018 - 03:49
Making sense?
User 16/10/2018 - 03:50
It does, thank you! So I just pasted the formula in A2 of my working spreadsheet and a window opened for me to select a file. Should I select the huge spreadsheet?
Excelchat Expert 16/10/2018 - 03:50
So, if we were to match against column L, then we would have wrote MATCH($K20,'[Merchant Vessels Torqued.xlsx]Merchant Vessels of the United '!$L$2:$L$368120,0)
Excelchat Expert 16/10/2018 - 03:51
You have to open the two files for the first time only. So, first open both files and then paste the formula in A2. After than you won't have to keep the huge file opened.
Excelchat Expert 16/10/2018 - 03:53
Once you paste the formula in A2, after than you can get other columns data just by dragging cell A2 across the row. (except for column K, there should already be a value, the official number).
User 16/10/2018 - 03:53
Okay - stand by, unfortunately I'm not able to have it work so far
Excelchat Expert 16/10/2018 - 03:54
I'm here.
Excelchat Expert 16/10/2018 - 03:55
If you are still being asked to select a file, please make sure that the file names on your pc is the same as the file you sent to me.
User 16/10/2018 - 03:55
Thanks - it looks like the name of the file in your formula is not the same as the current name for the file, so let me remedy that and see if it works.
Excelchat Expert 16/10/2018 - 03:55
Hmm. That is what I was suspecting.
User 16/10/2018 - 03:56
Allright, that's remedied, let's see how it goes.
User 16/10/2018 - 03:58
Allright, looks like we've got functionality, thank you! And this process, it's called index and matching?
Excelchat Expert 16/10/2018 - 03:58
The formula is named index/match, the process in a lookup process.
Excelchat Expert 16/10/2018 - 03:59
You got the desired result, right?
User 16/10/2018 - 03:59
I did. I will probably need another session to deal with another instance where I'm changing from two spreadsheets where all the columns are wildly different, so look forward to using you guys again soon!
Excelchat Expert 16/10/2018 - 04:00
If you need any clarification, you can ask me.
Excelchat Expert 16/10/2018 - 04:00
We have still 14 minutes left.
User 16/10/2018 - 04:02
Okay, thanks. So I want to do this with another file, which isn't so big: https://www.dropbox.com/s/87e49lmy4rt229v/full%20archive%20of%20form%202365494%20on%202018-10-16.xlsx?dl=0 . The issue is that the columns don't match up at all, is there a way to make this work: https://www.dropbox.com/s/87e49lmy4rt229v/full%20archive%20of%20form%202365494%20on%202018-10-16.xlsx?dl=0
Excelchat Expert 16/10/2018 - 04:03
With index/match the things we need to know is, what is the common between the two files/sheets/tables.
Excelchat Expert 16/10/2018 - 04:03
In your case, then common thing is official number, right?
User 16/10/2018 - 04:05
Exactly - the official number is the common thing, but in this case the columns are all over the place, so for example email is in column D in the one spreadsheet, and column E in the other.
Excelchat Expert 16/10/2018 - 04:05
That is not a problem at all.
Excelchat Expert 16/10/2018 - 04:07
The syntax of index/match is =INDEX(reference,MATCH(lookup_value,lookup,range,type))
Excelchat Expert 16/10/2018 - 04:08
Let's take the example you made, so, reference is what you want as the result, lookup-value is official number and lookup range is the column where this official number can be found in 2nd file.
User 16/10/2018 - 04:08
Ahh. So I can just do one for each individual cell in the row and then I'll have a complete set where everything transposes correctly. Thank you, I get it!
Excelchat Expert 16/10/2018 - 04:09
So, to make sure, if in the file where we are wring formula, email is in column D, we write the formula in column D but in the formula make the reference to column E of 2nd file.
Excelchat Expert 16/10/2018 - 04:09
Making sense? The formula will give us the result wherever we are placing it.
User 16/10/2018 - 04:09
Yup. I get it, because we're just doing one cell at a time. Awesome, this was fantastic and super helpful!
Excelchat Expert 16/10/2018 - 04:09
I'm glad to be able to help.
Excelchat Expert 16/10/2018 - 04:10
Anything else?
User 16/10/2018 - 04:10
Nope, that's it, you guys are awesome!
Excelchat Expert 16/10/2018 - 04:10
I'm glad to hear that.
Excelchat Expert 16/10/2018 - 04:10
Looking forward to have you with is in future.
Excelchat Expert 16/10/2018 - 04:10
Thanks for coming to Excelchat. Feel free to leave any comments or feedback. Have a nice day!

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