title

VLOOKUP in Excel | Tutorial for Beginners

description

🔥 Go from Excel novice to data analysis ninja in just 2 hours with my Excel for Beginners course: https://kevinstratvert.thinkific.com
In this step-by-step tutorial, learn how to use VLOOKUP, HLOOKUP, AND XLOOKUP in Microsoft Excel. With VLOOKUP, you can find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID.
👋 Additional resources:
- Sample file to follow along: https://1drv.ms/x/s!AmxrofZZlZ-whK4bPgcKpfbw0Iub5A?e=1NQpEF
- Learn the fundamentals of Excel in just 2 hours: https://kevinstratvert.thinkific.com
⌚ Timestamps
0:00 Introduction
1:47 Organize data properly for VLOOKUP
5:38 VLOOKUP Exact match
9:34 Improve error message with IFERROR
11:10 VLOOKUP Closest match
14:48 VLOOKUP Across sheets
15:43 HLOOKUP
18:21 XLOOKUP Simple example
22:53 XLOOKUP Combined with other functions
24:54 XLOOKUP Returns an array
26:42 XLOOKUP Match modes
28:30 XLOOKUP Search modes
31:24 Wrap up
📃 Watch related playlists and videos
- Playlist with all my videos on Excel: https://youtube.com/playlist?list=PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY
🚩 Connect with me on social:
- LinkedIn: https://www.linkedin.com/in/kevinstratvert/
- Twitter: https://twitter.com/kevstrat
- Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818
🔔 Subscribe to my YouTube channel
https://www.youtube.com/user/kevlers?sub_confirmation=1
🎬 Want to watch again? Navigate back to my YouTube channel quickly
http://www.kevinstratvert.com
🛍 Support me with your Amazon purchases: https://amzn.to/3kCP2yz
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#stratvert

detail

{'title': 'VLOOKUP in Excel | Tutorial for Beginners', 'heatmap': [{'end': 428.726, 'start': 327.247, 'weight': 0.759}, {'end': 523.828, 'start': 476.531, 'weight': 0.707}, {'end': 831.213, 'start': 752.137, 'weight': 0.759}, {'end': 949.933, 'start': 884.913, 'weight': 0.736}, {'end': 1080.345, 'start': 1056.947, 'weight': 0.73}, {'end': 1275.108, 'start': 1254.233, 'weight': 0.71}], 'summary': "This tutorial covers the definition, applications, and variations of vlookup in excel, including excel table creation, closest match lookup, and comparison with xlookup, highlighting its benefits and advantages over vlookup and hlookup, with practical examples and insights from the speaker's experience at microsoft.", 'chapters': [{'end': 264.352, 'segs': [{'end': 78.272, 'src': 'embed', 'start': 51.997, 'weight': 0, 'content': [{'end': 56.839, 'text': 'Or maybe you have two different tables of information and you want to bring them together.', 'start': 51.997, 'duration': 4.842}, {'end': 61.262, 'text': 'You can use VLOOKUP to join those two separate tables.', 'start': 57.3, 'duration': 3.962}, {'end': 63.203, 'text': "It's really versatile.", 'start': 61.882, 'duration': 1.321}, {'end': 67.725, 'text': "Today we're going to start off by going in depth on how you use VLOOKUP.", 'start': 63.923, 'duration': 3.802}, {'end': 75.649, 'text': 'Then I want to show you a variation of VLOOKUP called HLOOKUP where you can look up information in a horizontal list.', 'start': 68.365, 'duration': 7.284}, {'end': 78.272, 'text': "then at the very end I'll save the best.", 'start': 76.369, 'duration': 1.903}], 'summary': 'Learn vlookup and hlookup functions for joining and retrieving data.', 'duration': 26.275, 'max_score': 51.997, 'thumbnail': ''}, {'end': 146.909, 'src': 'embed', 'start': 123.61, 'weight': 3, 'content': [{'end': 131.076, 'text': "We're going to work our way through here and by the end of this video, you are going to be an expert in looking up using VLOOKUP,", 'start': 123.61, 'duration': 7.466}, {'end': 133.018, 'text': 'HLOOKUP and also XLOOKUP.', 'start': 131.076, 'duration': 1.942}, {'end': 139.763, 'text': "Okay, so let's get started with a really simple example that'll demonstrate how you can use VLOOKUP.", 'start': 133.698, 'duration': 6.065}, {'end': 143.807, 'text': 'Right here on this sheet, I have a table of customer information.', 'start': 140.424, 'duration': 3.383}, {'end': 146.909, 'text': 'Over on the left-hand side, I have the customer ID.', 'start': 144.307, 'duration': 2.602}], 'summary': 'Become an expert in vlookup, hlookup, and xlookup by the end of the video.', 'duration': 23.299, 'max_score': 123.61, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw123610.jpg'}, {'end': 200.217, 'src': 'embed', 'start': 168.427, 'weight': 2, 'content': [{'end': 170.228, 'text': "And we're going to dive in in just a moment.", 'start': 168.427, 'duration': 1.801}, {'end': 176.893, 'text': 'but before we dive into the nitty gritty of how you use VLOOKUP, I want to take a moment to talk about how you should organize your data.', 'start': 170.228, 'duration': 6.665}, {'end': 185.202, 'text': "Over in the lookup table over on the left hand side, you want to make sure that the value that you're looking up is the leftmost column.", 'start': 177.714, 'duration': 7.488}, {'end': 193.089, 'text': "So here I'm looking up the customer ID and over here in this table the customer ID right now is already the leftmost column.", 'start': 185.642, 'duration': 7.447}, {'end': 200.217, 'text': 'So if you have to rearrange your data so the lookup value is over on the left, feel free to do that before using VLOOKUP.', 'start': 193.43, 'duration': 6.787}], 'summary': 'Organize data with lookup value as leftmost column for vlookup.', 'duration': 31.79, 'max_score': 168.427, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw168427.jpg'}, {'end': 255.885, 'src': 'embed', 'start': 225.453, 'weight': 4, 'content': [{'end': 227.574, 'text': "So let's say that it's not in ascending order.", 'start': 225.453, 'duration': 2.121}, {'end': 228.855, 'text': "It's easy to change that.", 'start': 227.754, 'duration': 1.101}, {'end': 231.797, 'text': 'You can go up to the header of your column.', 'start': 229.395, 'duration': 2.402}, {'end': 237.283, 'text': 'You could right click on that and then you can go down to sort and here you could sort A to Z.', 'start': 231.958, 'duration': 5.325}, {'end': 242.768, 'text': 'Also, alternatively, you can also go to the data tab up on top and here you can click on filter.', 'start': 237.283, 'duration': 5.485}, {'end': 245.19, 'text': "So here I'll toggle it off and I'll toggle it back on.", 'start': 242.888, 'duration': 2.302}, {'end': 250.255, 'text': 'And over here I can click on the filter and here too I could also sort from smallest to largest.', 'start': 245.63, 'duration': 4.625}, {'end': 255.885, 'text': 'Next, you also want to make sure that there is a common field that you can use to make a connection.', 'start': 251.041, 'duration': 4.844}], 'summary': 'Demonstrates sorting data in excel by ascending order and using filters for data manipulation.', 'duration': 30.432, 'max_score': 225.453, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw225453.jpg'}], 'start': 3.902, 'title': 'Using vlookup in excel', 'summary': "Explores the definition, applications, and variations of vlookup, with insights from the speaker's experience at microsoft. it covers the step-by-step process, tips on data organization, and a demonstration of a simple example with a sample workbook for practical learning.", 'chapters': [{'end': 90.707, 'start': 3.902, 'title': 'Using vlookup in excel', 'summary': "Explores the versatile vlookup function in microsoft excel, including its definition, applications, and variations like hlookup and xlookup, with insights from the speaker's experience working at microsoft.", 'duration': 86.805, 'highlights': ['VLOOKUP is a popular function in Excel, used by the speaker during their time at Microsoft.', 'VLOOKUP stands for vertical lookup, enabling users to retrieve information from a vertical list, such as customer names corresponding to their IDs.', 'The chapter delves into the in-depth usage of VLOOKUP, introduces HLOOKUP for horizontal list lookup, and highlights XLOOKUP as an improved alternative.', 'VLOOKUP can be utilized to merge separate tables of information, showcasing its versatility and practical applications.']}, {'end': 264.352, 'start': 90.807, 'title': 'Vlookup and data organization in excel', 'summary': 'Covers the step-by-step process of using vlookup, including tips on organizing data and ensuring the lookup value is in the leftmost column and in ascending order, with a demonstration of a simple example and a sample workbook provided for practical learning.', 'duration': 173.545, 'highlights': ['The importance of organizing data for VLOOKUP, including ensuring the lookup value is in the leftmost column and in ascending order, and having a common field for connection.', 'Demonstration of a simple example of using VLOOKUP to look up customer information based on a customer ID.', 'Providing a sample workbook for practical learning and following along with the video.', 'Tips on sorting data in Excel, including sorting columns in ascending order and using the filter option.', 'Emphasizing the need for the lookup column to be sorted in ascending order, facilitating accurate VLOOKUP results.']}], 'duration': 260.45, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw3902.jpg', 'highlights': ['The chapter delves into the in-depth usage of VLOOKUP, introduces HLOOKUP for horizontal list lookup, and highlights XLOOKUP as an improved alternative.', 'VLOOKUP can be utilized to merge separate tables of information, showcasing its versatility and practical applications.', 'The importance of organizing data for VLOOKUP, including ensuring the lookup value is in the leftmost column and in ascending order, and having a common field for connection.', 'Demonstration of a simple example of using VLOOKUP to look up customer information based on a customer ID.', 'Tips on sorting data in Excel, including sorting columns in ascending order and using the filter option.']}, {'end': 670.631, 'segs': [{'end': 325.087, 'src': 'embed', 'start': 284.108, 'weight': 0, 'content': [{'end': 287.911, 'text': "but it makes it a lot easier and it'll also help you avoid some errors.", 'start': 284.108, 'duration': 3.803}, {'end': 291.694, 'text': 'So you might be wondering well, how do I turn data into a table?', 'start': 288.331, 'duration': 3.363}, {'end': 296.758, 'text': "Well, let's jump to this next sheet here called VLOOKUP Make Table, and I'll show you how you can make a table.", 'start': 291.794, 'duration': 4.964}, {'end': 302.143, 'text': "So right now this is organized in what looks like a table, but it's not an official Excel table.", 'start': 297.219, 'duration': 4.924}, {'end': 304.463, 'text': "And it's really easy to turn this into a table.", 'start': 302.543, 'duration': 1.92}, {'end': 310.124, 'text': 'Right here, once I have all of the data selected, you can go up to insert up here on the top tabs.', 'start': 305.163, 'duration': 4.961}, {'end': 313.085, 'text': "And here's an option to convert it into a table.", 'start': 310.604, 'duration': 2.481}, {'end': 317.165, 'text': 'When I hover over, you also see that the shortcut key is control T.', 'start': 313.605, 'duration': 3.56}, {'end': 318.946, 'text': 'So I could also press that to make it a table.', 'start': 317.165, 'duration': 1.781}, {'end': 320.566, 'text': "Let's try the shortcut key.", 'start': 319.386, 'duration': 1.18}, {'end': 322.026, 'text': "I'll press control T.", 'start': 320.626, 'duration': 1.4}, {'end': 325.087, 'text': "And here it says, what's your table? And so I've already selected it.", 'start': 322.026, 'duration': 3.061}], 'summary': 'Learn to convert data into a table in excel using the shortcut key control t.', 'duration': 40.979, 'max_score': 284.108, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw284108.jpg'}, {'end': 428.726, 'src': 'heatmap', 'start': 327.247, 'weight': 0.759, 'content': [{'end': 328.507, 'text': 'And my table has headers.', 'start': 327.247, 'duration': 1.26}, {'end': 331.028, 'text': "So I'll make sure to check this box and then click on OK.", 'start': 328.527, 'duration': 2.501}, {'end': 331.728, 'text': 'And look at that.', 'start': 331.268, 'duration': 0.46}, {'end': 334.411, 'text': 'I now have a table of information.', 'start': 332.228, 'duration': 2.183}, {'end': 336.313, 'text': 'So that worked exactly how I wanted it to.', 'start': 334.471, 'duration': 1.842}, {'end': 338.395, 'text': "Now let's go back to the previous sheet.", 'start': 336.873, 'duration': 1.522}, {'end': 344.502, 'text': "We've gone through a bunch of information about how you should structure your data before running a VLOOKUP and all.", 'start': 339.076, 'duration': 5.426}, {'end': 349.227, 'text': "that's very important because this ensures that when we run the VLOOKUP, we'll be running it correctly.", 'start': 344.502, 'duration': 4.725}, {'end': 352.73, 'text': 'Next, I want to enter my VLOOKUP function.', 'start': 349.887, 'duration': 2.843}, {'end': 356.733, 'text': 'So over here for this cell where I want the name to appear,', 'start': 353.331, 'duration': 3.402}, {'end': 361.756, 'text': "I'll click in here and then let's go up to the formula bar and I'm going to click on this FX symbol.", 'start': 356.733, 'duration': 5.023}, {'end': 362.496, 'text': "Let's click on that.", 'start': 361.876, 'duration': 0.62}, {'end': 371.761, 'text': 'This opens up a prompt where we can enter in our function and we want to use VLOOKUP, so right up here type in VLOOKUP and then click on go.', 'start': 363.356, 'duration': 8.405}, {'end': 375.003, 'text': "Right here I see the function, so let's double click on that.", 'start': 372.381, 'duration': 2.622}, {'end': 380.527, 'text': 'This now opens up another prompt where I can enter in all of my function arguments.', 'start': 375.783, 'duration': 4.744}, {'end': 383.93, 'text': "And we're going to walk through these to help you understand what they mean.", 'start': 381.028, 'duration': 2.902}, {'end': 388.494, 'text': 'So let me pull this down a little bit so we can see it and we can see all of our data up above.', 'start': 384.111, 'duration': 4.383}, {'end': 395.941, 'text': "First, I need to enter a lookup value and this is bolded here, meaning that it's a required argument that I need to enter.", 'start': 389.275, 'duration': 6.666}, {'end': 399.785, 'text': 'And the lookup value, well, I want to look up customer ID number four.', 'start': 396.502, 'duration': 3.283}, {'end': 403.327, 'text': "So I'm going to click over in this cell and that's now selected F2.", 'start': 400.305, 'duration': 3.022}, {'end': 404.468, 'text': "That's my lookup value.", 'start': 403.447, 'duration': 1.021}, {'end': 406.389, 'text': "We're going to look up 4.", 'start': 404.508, 'duration': 1.881}, {'end': 410.092, 'text': "Right down below here I can verify that it's looking up 4.", 'start': 406.389, 'duration': 3.703}, {'end': 411.833, 'text': "Here it's showing me the value right here.", 'start': 410.092, 'duration': 1.741}, {'end': 417.337, 'text': "Next it asks me to enter the table array, and this is where I'm doing the lookup.", 'start': 412.534, 'duration': 4.803}, {'end': 420.319, 'text': 'And over here I want to look up against this table.', 'start': 417.778, 'duration': 2.541}, {'end': 423.222, 'text': "So here I'll select the entire table.", 'start': 420.94, 'duration': 2.282}, {'end': 428.726, 'text': 'And so one of the interesting things is since we define this as a table, here it refers to that table.', 'start': 423.642, 'duration': 5.084}], 'summary': 'Demonstration of creating a table and using vlookup function to retrieve data.', 'duration': 101.479, 'max_score': 327.247, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw327247.jpg'}, {'end': 388.494, 'src': 'embed', 'start': 363.356, 'weight': 1, 'content': [{'end': 371.761, 'text': 'This opens up a prompt where we can enter in our function and we want to use VLOOKUP, so right up here type in VLOOKUP and then click on go.', 'start': 363.356, 'duration': 8.405}, {'end': 375.003, 'text': "Right here I see the function, so let's double click on that.", 'start': 372.381, 'duration': 2.622}, {'end': 380.527, 'text': 'This now opens up another prompt where I can enter in all of my function arguments.', 'start': 375.783, 'duration': 4.744}, {'end': 383.93, 'text': "And we're going to walk through these to help you understand what they mean.", 'start': 381.028, 'duration': 2.902}, {'end': 388.494, 'text': 'So let me pull this down a little bit so we can see it and we can see all of our data up above.', 'start': 384.111, 'duration': 4.383}], 'summary': 'Using vlookup function to retrieve and enter function arguments.', 'duration': 25.138, 'max_score': 363.356, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw363356.jpg'}, {'end': 530.977, 'src': 'heatmap', 'start': 462.082, 'weight': 3, 'content': [{'end': 465.564, 'text': "One downside with that is, let's say, you have content underneath your table.", 'start': 462.082, 'duration': 3.482}, {'end': 468.346, 'text': 'Those might also be included in the VLOOKUP.', 'start': 466.144, 'duration': 2.202}, {'end': 473.129, 'text': 'So once again, as a best practice, create a table and then you could just search across that table.', 'start': 468.586, 'duration': 4.543}, {'end': 475.61, 'text': 'It tends to avoid errors in the long run.', 'start': 473.549, 'duration': 2.061}, {'end': 482.355, 'text': "I've now selected my table and next there's another argument here called the column index number.", 'start': 476.531, 'duration': 5.824}, {'end': 487.039, 'text': "So right now I'm looking for the value 4 in this table over here.", 'start': 482.936, 'duration': 4.103}, {'end': 493.243, 'text': "So it looks over in the first column and it says, is there a number 4? And here it'll find the number 4.", 'start': 487.099, 'duration': 6.144}, {'end': 497.366, 'text': 'Now, this next argument says well, which column do you then want to return?', 'start': 493.243, 'duration': 4.123}, {'end': 506.092, 'text': "So I've selected this table and it has three columns one, two, three and I want to send the name back, and the name is the second column.", 'start': 497.866, 'duration': 8.226}, {'end': 508.553, 'text': "So right here, I'll enter the number two.", 'start': 506.692, 'duration': 1.861}, {'end': 517.102, 'text': "And last, there's something called range lookup and in a moment I'll go into more detail on what that means, but for now let's set it to false.", 'start': 509.554, 'duration': 7.548}, {'end': 519.083, 'text': 'We want this to be an exact match.', 'start': 517.202, 'duration': 1.881}, {'end': 523.828, 'text': 'What I mean by that is, when it finds customer ID number four,', 'start': 519.645, 'duration': 4.183}, {'end': 530.977, 'text': "it'll look in this list and only if it finds the exact value for number four will it return the customer name.", 'start': 523.828, 'duration': 7.149}], 'summary': 'Best practice: use table for vlookup to avoid errors in the long run, with a value 4, returning the name as an exact match.', 'duration': 68.895, 'max_score': 462.082, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw462082.jpg'}, {'end': 536.498, 'src': 'embed', 'start': 506.692, 'weight': 4, 'content': [{'end': 508.553, 'text': "So right here, I'll enter the number two.", 'start': 506.692, 'duration': 1.861}, {'end': 517.102, 'text': "And last, there's something called range lookup and in a moment I'll go into more detail on what that means, but for now let's set it to false.", 'start': 509.554, 'duration': 7.548}, {'end': 519.083, 'text': 'We want this to be an exact match.', 'start': 517.202, 'duration': 1.881}, {'end': 523.828, 'text': 'What I mean by that is, when it finds customer ID number four,', 'start': 519.645, 'duration': 4.183}, {'end': 530.977, 'text': "it'll look in this list and only if it finds the exact value for number four will it return the customer name.", 'start': 523.828, 'duration': 7.149}, {'end': 536.498, 'text': "Let's say I sent in customer ID 3.5 or 4.5 or customer ID 6.", 'start': 531.437, 'duration': 5.061}], 'summary': 'Demonstrating excel lookup function with range lookup set to false for exact matches.', 'duration': 29.806, 'max_score': 506.692, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw506692.jpg'}, {'end': 654.119, 'src': 'embed', 'start': 631.848, 'weight': 5, 'content': [{'end': 639.79, 'text': 'So the first one is if there is no error, what should it display? And if there is no error, well, I just want to show the output of the VLOOKUP.', 'start': 631.848, 'duration': 7.942}, {'end': 641.271, 'text': "So I'll leave the VLOOKUP here.", 'start': 639.83, 'duration': 1.441}, {'end': 643.432, 'text': "But let's say there is an error.", 'start': 642.051, 'duration': 1.381}, {'end': 647.054, 'text': "I'll enter in a comma here and this is where I enter my second argument.", 'start': 643.732, 'duration': 3.322}, {'end': 651.377, 'text': "I could just put in some quotes and maybe I just say not found just so it's a little bit friendlier.", 'start': 647.595, 'duration': 3.782}, {'end': 654.119, 'text': "I'll close the quotes and then I'll close the parentheses.", 'start': 651.498, 'duration': 2.621}], 'summary': 'Demonstrating vlookup with and without error handling', 'duration': 22.271, 'max_score': 631.848, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw631848.jpg'}], 'start': 264.852, 'title': 'Vlookup and excel tables', 'summary': 'Explains the importance of creating tables in excel before using vlookup, highlighting benefits such as ease, error avoidance, and automatic data inclusion. it also details the process of using vlookup function step by step, and demonstrates the enhancement of error handling with the iferror function, resulting in a more user-friendly display of errors.', 'chapters': [{'end': 482.355, 'start': 264.852, 'title': 'Vlookup and creating excel tables', 'summary': 'Explains the importance of creating tables in excel before using vlookup, highlighting the benefits of ease, error avoidance, and automatic data inclusion, also detailing the process of converting data into a table and entering vlookup function step by step.', 'duration': 217.503, 'highlights': ['The importance of creating tables in Excel before using VLOOKUP Creating a table in Excel before using VLOOKUP is emphasized due to the ease, error avoidance, and automatic data inclusion it provides.', "Process of converting data into a table in Excel The process of converting data into a table in Excel is detailed, including selecting the data, using the 'insert' option, and checking the 'table has headers' box.", 'Entering VLOOKUP function step by step The step-by-step process of entering the VLOOKUP function is explained, including entering the lookup value, selecting the table array, and understanding the column index number argument.']}, {'end': 670.631, 'start': 482.936, 'title': 'Using vlookup to retrieve data', 'summary': 'Demonstrates the use of vlookup function to retrieve data from a table, setting range lookup to false for exact matching, and enhancing error handling with the iferror function, resulting in a more user-friendly display of errors.', 'duration': 187.695, 'highlights': ['VLOOKUP function used to retrieve data from a table The speaker demonstrates using the VLOOKUP function to search for a specific value in a table and return the corresponding value from a specified column.', 'Setting range lookup to false for exact matching The chapter emphasizes the importance of setting range lookup to false to ensure exact matching when using VLOOKUP, preventing errors for non-matching values.', 'Enhancing error handling with the IFERROR function The speaker illustrates using the IFERROR function to improve error handling in VLOOKUP, displaying a more user-friendly message when a value is not found in the table.']}], 'duration': 405.779, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw264852.jpg', 'highlights': ['The importance of creating tables in Excel before using VLOOKUP is emphasized due to the ease, error avoidance, and automatic data inclusion it provides.', 'The step-by-step process of entering the VLOOKUP function is explained, including entering the lookup value, selecting the table array, and understanding the column index number argument.', "The process of converting data into a table in Excel is detailed, including selecting the data, using the 'insert' option, and checking the 'table has headers' box.", 'The speaker demonstrates using the VLOOKUP function to search for a specific value in a table and return the corresponding value from a specified column.', 'The chapter emphasizes the importance of setting range lookup to false to ensure exact matching when using VLOOKUP, preventing errors for non-matching values.', 'The speaker illustrates using the IFERROR function to improve error handling in VLOOKUP, displaying a more user-friendly message when a value is not found in the table.']}, {'end': 878.631, 'segs': [{'end': 737.389, 'src': 'embed', 'start': 671.451, 'weight': 1, 'content': [{'end': 674.573, 'text': 'Next, I want to show you how closest match works.', 'start': 671.451, 'duration': 3.122}, {'end': 679.696, 'text': 'In this example, we were looking for an exact match, but when would you use a closest match?', 'start': 674.933, 'duration': 4.763}, {'end': 685.62, 'text': "And for that let's go down to the different worksheets over here and click on the one called VLOOKUP closest match.", 'start': 680.116, 'duration': 5.504}, {'end': 690.365, 'text': 'On this next worksheet, I have two different tables of information.', 'start': 686.54, 'duration': 3.825}, {'end': 692.828, 'text': 'I have cookie orders over here.', 'start': 690.966, 'duration': 1.862}, {'end': 697.995, 'text': 'So here I have an order ID and then I have a certain number of cookies that that customer ordered.', 'start': 692.928, 'duration': 5.067}, {'end': 702.38, 'text': 'And I want to offer some free cookies just as an incentive to order more.', 'start': 698.535, 'duration': 3.845}, {'end': 708.826, 'text': "So here for example, if you order 100 cookies, we'll throw in 5 for free, so you get 5% free cookies.", 'start': 703.041, 'duration': 5.785}, {'end': 716.053, 'text': 'Or if you order 400 cookies, you get 20 free cookies, so just as an incentive to drive more people to order cookies.', 'start': 709.467, 'duration': 6.586}, {'end': 723.14, 'text': "Now I want to put in how many free cookies I should include in each order, but I don't want to have to go through and say, hey,", 'start': 716.794, 'duration': 6.346}, {'end': 726.443, 'text': 'look at how many cookies they ordered and then figure out where that sits on this table.', 'start': 723.14, 'duration': 3.303}, {'end': 731.205, 'text': 'And in fact, when you look at this, here someone placed an order for 26 cookies.', 'start': 727.043, 'duration': 4.162}, {'end': 734.507, 'text': 'There is no 26 in this other table.', 'start': 731.666, 'duration': 2.841}, {'end': 737.389, 'text': 'This is where we can use closest match.', 'start': 735.108, 'duration': 2.281}], 'summary': 'Demonstrating the use of closest match in vlookup for offering free cookies based on customer orders.', 'duration': 65.938, 'max_score': 671.451, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw671451.jpg'}, {'end': 782.573, 'src': 'embed', 'start': 755.939, 'weight': 3, 'content': [{'end': 761.042, 'text': 'This opens up insert function and right down here you should see VLOOKUP as a recent function.', 'start': 755.939, 'duration': 5.103}, {'end': 762.102, 'text': "I'll click on this one.", 'start': 761.302, 'duration': 0.8}, {'end': 765.224, 'text': 'Once again, we can enter in the different function arguments.', 'start': 762.663, 'duration': 2.561}, {'end': 770.326, 'text': 'Here I need to enter my lookup value and I want to look up how many cookies they ordered.', 'start': 765.724, 'duration': 4.602}, {'end': 777.77, 'text': 'So if someone orders 26, well how many free cookies should we give? So here the lookup value is 26.', 'start': 770.386, 'duration': 7.384}, {'end': 782.573, 'text': "Right down here I need to select the table array and I'm looking it up against this table over here.", 'start': 777.77, 'duration': 4.803}], 'summary': 'Using vlookup to find number of free cookies based on orders.', 'duration': 26.634, 'max_score': 755.939, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw755939.jpg'}, {'end': 840.595, 'src': 'heatmap', 'start': 752.137, 'weight': 0, 'content': [{'end': 754.558, 'text': "Once again, let's go up to the formula bar and click on the FX.", 'start': 752.137, 'duration': 2.421}, {'end': 761.042, 'text': 'This opens up insert function and right down here you should see VLOOKUP as a recent function.', 'start': 755.939, 'duration': 5.103}, {'end': 762.102, 'text': "I'll click on this one.", 'start': 761.302, 'duration': 0.8}, {'end': 765.224, 'text': 'Once again, we can enter in the different function arguments.', 'start': 762.663, 'duration': 2.561}, {'end': 770.326, 'text': 'Here I need to enter my lookup value and I want to look up how many cookies they ordered.', 'start': 765.724, 'duration': 4.602}, {'end': 777.77, 'text': 'So if someone orders 26, well how many free cookies should we give? So here the lookup value is 26.', 'start': 770.386, 'duration': 7.384}, {'end': 782.573, 'text': "Right down here I need to select the table array and I'm looking it up against this table over here.", 'start': 777.77, 'duration': 4.803}, {'end': 784.314, 'text': "So here I'll select this table.", 'start': 782.773, 'duration': 1.541}, {'end': 788.956, 'text': 'Right down here it says well, what index or what column do you want to send back?', 'start': 785.293, 'duration': 3.663}, {'end': 793.919, 'text': "So here I'll look up 26 and here it's going to look it up against the leftmost column.", 'start': 789.476, 'duration': 4.443}, {'end': 799.283, 'text': 'And when it finds out what bucket it falls in, then I want to give back how many free cookies I should include.', 'start': 794.079, 'duration': 5.204}, {'end': 800.344, 'text': "And that's the second column.", 'start': 799.303, 'duration': 1.041}, {'end': 801.525, 'text': "So I'll enter a two here.", 'start': 800.404, 'duration': 1.121}, {'end': 807.561, 'text': 'Now with range lookup, this time instead of doing an exact match, I want to use a closest match.', 'start': 802.338, 'duration': 5.223}, {'end': 813.804, 'text': "And if I don't enter anything at all in this field, it'll default to using closest match.", 'start': 808.021, 'duration': 5.783}, {'end': 816.846, 'text': "So I'll just leave it blank and then let's click on OK.", 'start': 814.085, 'duration': 2.761}, {'end': 818.067, 'text': 'And look at that.', 'start': 817.246, 'duration': 0.821}, {'end': 824.551, 'text': 'Using the VLOOKUP, I now know how many free cookies I should include as each one of these orders.', 'start': 818.287, 'duration': 6.264}, {'end': 831.213, 'text': "And so you might be wondering, well, how does closest match work exactly? So, here let's just take a look at this example.", 'start': 825.231, 'duration': 5.982}, {'end': 837.415, 'text': 'A customer ordered 26 cookies and that falls between 0 and 100.', 'start': 831.473, 'duration': 5.942}, {'end': 840.595, 'text': "So, it's greater than 0, but it's less than 100.", 'start': 837.415, 'duration': 3.18}], 'summary': 'Using vlookup, determine free cookies for orders. example: 26 cookies falls between 0 and 100.', 'duration': 88.458, 'max_score': 752.137, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw752137.jpg'}], 'start': 671.451, 'title': 'Vlookup for closest match', 'summary': 'Explains the concept of closest match using an example of offering free cookies based on the number of cookies ordered, demonstrating the use of vlookup in excel to perform a closest match lookup for calculating taxes and determining the number of free cookies to include in orders.', 'chapters': [{'end': 737.389, 'start': 671.451, 'title': 'Vlookup closest match example', 'summary': 'Explains the concept of closest match using an example of offering free cookies based on the number of cookies ordered, demonstrating the use of vlookup to find the closest match for orders not exactly matching the lookup table.', 'duration': 65.938, 'highlights': ['VLOOKUP is used to find the closest match for orders that do not exactly match the lookup table, providing flexibility and automation in offering free cookies based on the number of cookies ordered.', 'The example demonstrates incentivizing customers by offering free cookies based on the quantity of cookies ordered, such as offering 5% free cookies for 100 cookies ordered and 20 free cookies for 400 cookies ordered.', 'The need for using closest match is highlighted through the example of an order for 26 cookies, which does not have an exact match in the lookup table, showcasing the practical application of this concept.']}, {'end': 793.919, 'start': 737.989, 'title': 'Using vlookup in excel', 'summary': 'Demonstrates the use of vlookup in excel to perform a closest match lookup for calculating taxes and determining the number of free cookies to give based on the quantity ordered.', 'duration': 55.93, 'highlights': ['The VLOOKUP function is used to perform a closest match lookup for calculating taxes and determining the number of free cookies to give based on the quantity ordered.', 'An example scenario is presented where the lookup value is 26, and the function is utilized to look up the corresponding information from a specified table array.', 'The process of entering the VLOOKUP formula and its function arguments is explained, emphasizing the lookup value, table array, and the index or column to return.', 'Demonstration of using the VLOOKUP function in Excel to perform a closest match lookup for determining the number of free cookies to give based on the quantity ordered.']}, {'end': 878.631, 'start': 794.079, 'title': 'Using vlookup and range lookup in excel', 'summary': 'Explains how to use vlookup and range lookup in excel to determine the number of free cookies to include in orders, showcasing examples of closest match calculation for different order quantities.', 'duration': 84.552, 'highlights': ['The VLOOKUP function is used to determine the number of free cookies to include in each order, based on specific criteria entered into the function, such as the order quantity and a predefined table of values.', 'Range lookup in VLOOKUP allows for a closest match calculation, where the function finds the closest value that is less than or equal to the specified criteria, as demonstrated through examples of order quantities falling within different ranges and the resulting number of free cookies to be included.']}], 'duration': 207.18, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw671451.jpg', 'highlights': ['VLOOKUP is used to find the closest match for orders, providing flexibility and automation in offering free cookies.', 'The example demonstrates incentivizing customers by offering free cookies based on the quantity of cookies ordered.', 'The need for using closest match is highlighted through the example of an order for 26 cookies.', 'The VLOOKUP function is used to perform a closest match lookup for calculating taxes and determining the number of free cookies to give based on the quantity ordered.', 'Demonstration of using the VLOOKUP function in Excel to perform a closest match lookup for determining the number of free cookies to give based on the quantity ordered.', 'Range lookup in VLOOKUP allows for a closest match calculation, where the function finds the closest value that is less than or equal to the specified criteria.']}, {'end': 1307.849, 'segs': [{'end': 955.256, 'src': 'heatmap', 'start': 879.091, 'weight': 2, 'content': [{'end': 884.913, 'text': "Now, hopefully you're starting to get a feel for how VLOOKUP works, and the great thing is, when you use VLOOKUP,", 'start': 879.091, 'duration': 5.822}, {'end': 887.654, 'text': "your two tables don't have to be on the same sheet.", 'start': 884.913, 'duration': 2.741}, {'end': 890.615, 'text': 'In fact, you can have them on completely separate sheets.', 'start': 887.994, 'duration': 2.621}, {'end': 897.537, 'text': "Let's jump over to the next sheet, called VLOOKUP Across Sheets, and here's the same exact example.", 'start': 891.195, 'duration': 6.342}, {'end': 901.518, 'text': "except with this example, I don't have the other table sitting right next to it.", 'start': 897.537, 'duration': 3.981}, {'end': 907.48, 'text': "Instead, the other table is on this second sheet, so let's see how we could run the VLOOKUP to get the same results.", 'start': 901.598, 'duration': 5.882}, {'end': 909.66, 'text': "Here again, I'll click into this cell.", 'start': 908.18, 'duration': 1.48}, {'end': 911.741, 'text': "Let's click on FX up on the formula bar.", 'start': 909.72, 'duration': 2.021}, {'end': 913.741, 'text': 'This opens up the insert function.', 'start': 912.161, 'duration': 1.58}, {'end': 915.081, 'text': "Let's select VLOOKUP.", 'start': 913.841, 'duration': 1.24}, {'end': 917.002, 'text': 'Right here, the lookup value.', 'start': 915.781, 'duration': 1.221}, {'end': 919.162, 'text': "Once again, I'm looking up the cookies ordered.", 'start': 917.082, 'duration': 2.08}, {'end': 921.902, 'text': 'Next, I need to type in the table array.', 'start': 919.762, 'duration': 2.14}, {'end': 924.743, 'text': "So I'll click over here and then let's jump to this next sheet.", 'start': 921.942, 'duration': 2.801}, {'end': 927.043, 'text': 'And this is the table I want to look up against.', 'start': 925.103, 'duration': 1.94}, {'end': 928.364, 'text': "So I'll select that.", 'start': 927.503, 'duration': 0.861}, {'end': 932.084, 'text': 'And here, just like we did previously, I want to return the second column.', 'start': 928.784, 'duration': 3.3}, {'end': 935.985, 'text': "And here with the range lookup, I'll leave that blank so it'll be a closest match.", 'start': 932.584, 'duration': 3.401}, {'end': 937.666, 'text': "Next, let's click on OK.", 'start': 936.365, 'duration': 1.301}, {'end': 944.05, 'text': "And look at that, the VLOOKUP works just like before, except this time it's working across different sheets.", 'start': 938.246, 'duration': 5.804}, {'end': 949.933, 'text': "Next, let's go down and let's click into HLOOKUP to see how we can do a horizontal lookup.", 'start': 944.63, 'duration': 5.303}, {'end': 955.256, 'text': "Now, so far we've been doing vertical lookups, our tables organized in a vertical list.", 'start': 950.353, 'duration': 4.903}], 'summary': 'Vlookup can work across different sheets, enabling separate table usage.', 'duration': 76.165, 'max_score': 879.091, 'thumbnail': ''}, {'end': 1084.547, 'src': 'heatmap', 'start': 1056.947, 'weight': 0.73, 'content': [{'end': 1062.311, 'text': "Here as I go down, if I click into this cell, you see that it automatically adjusts the table that it's looking against.", 'start': 1056.947, 'duration': 5.364}, {'end': 1063.532, 'text': "I don't want it to do that.", 'start': 1062.492, 'duration': 1.04}, {'end': 1067.695, 'text': 'So instead, I can go up to the top and I need to make it an absolute reference.', 'start': 1063.672, 'duration': 4.023}, {'end': 1071.358, 'text': 'So as I pull this formula down, it continues to look at this table.', 'start': 1067.735, 'duration': 3.623}, {'end': 1074, 'text': 'To do that, I can press the F4 key.', 'start': 1071.898, 'duration': 2.102}, {'end': 1077.523, 'text': "I could press the F4 key again, and then I'll hit enter.", 'start': 1074.34, 'duration': 3.183}, {'end': 1080.345, 'text': 'And now you see that the formula works properly.', 'start': 1077.863, 'duration': 2.482}, {'end': 1084.547, 'text': "That's one of the downsides of using a horizontal table in Excel.", 'start': 1080.965, 'duration': 3.582}], 'summary': 'Adjusting excel table for absolute reference using f4 key.', 'duration': 27.6, 'max_score': 1056.947, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw1056947.jpg'}, {'end': 1126.207, 'src': 'embed', 'start': 1103.086, 'weight': 0, 'content': [{'end': 1110.554, 'text': "Now, by now, your lookup skills should be getting pretty good, but what if I told you there's an even better version of looking up,", 'start': 1103.086, 'duration': 7.468}, {'end': 1112.216, 'text': "and that's called XLOOKUP?", 'start': 1110.554, 'duration': 1.662}, {'end': 1118.062, 'text': 'It can do everything that VLOOKUP and HLOOKUP can do, but it can do even more.', 'start': 1112.936, 'duration': 5.126}, {'end': 1126.207, 'text': "On its own, it can search on vertical and horizontal lists, so you don't need separate functions and it does a lot more than just that.", 'start': 1118.622, 'duration': 7.585}], 'summary': 'Xlookup is an advanced version of vlookup and hlookup, capable of searching on vertical and horizontal lists and performing additional functions.', 'duration': 23.121, 'max_score': 1103.086, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw1103086.jpg'}, {'end': 1283.238, 'src': 'heatmap', 'start': 1254.233, 'weight': 0.71, 'content': [{'end': 1257.334, 'text': 'Now previously with VLOOKUP, we selected this entire table.', 'start': 1254.233, 'duration': 3.101}, {'end': 1259.055, 'text': "We don't have to do that anymore.", 'start': 1257.574, 'duration': 1.481}, {'end': 1261.256, 'text': "Instead, I'm just going to select this column.", 'start': 1259.475, 'duration': 1.781}, {'end': 1264.298, 'text': 'I want to look up in this column to find that value.', 'start': 1261.356, 'duration': 2.942}, {'end': 1268.06, 'text': "So one of the nice things is once again, I don't have to rearrange my data.", 'start': 1264.878, 'duration': 3.182}, {'end': 1269.321, 'text': 'over here.', 'start': 1268.84, 'duration': 0.481}, {'end': 1271.524, 'text': 'I want to select what I want to return.', 'start': 1269.321, 'duration': 2.203}, {'end': 1275.108, 'text': "so I'll click over here and I want to return the revenue per cookie.", 'start': 1271.524, 'duration': 3.584}, {'end': 1283.238, 'text': "so in this same table, the revenue is over here, so I'll select that column, so I don't have to worry about entering in a column index or a row index.", 'start': 1275.108, 'duration': 8.13}], 'summary': 'Using index/match instead of vlookup for specific column lookup.', 'duration': 29.005, 'max_score': 1254.233, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw1254233.jpg'}], 'start': 879.091, 'title': 'Excel lookup functions', 'summary': 'Explains using vlookup and hlookup across sheets, and discusses the advantages of xlookup over vlookup and hlookup, emphasizing its intuitive functionality and built-in error handling.', 'chapters': [{'end': 1046.52, 'start': 879.091, 'title': 'Vlookup and hlookup across sheets', 'summary': 'Explains how to use vlookup and hlookup across different sheets, demonstrating the process and the functionality of these lookup functions, with an emphasis on using them against horizontally organized data.', 'duration': 167.429, 'highlights': ['VLOOKUP and HLOOKUP can be used across different sheets, allowing for lookup between separate sheets. Demonstrates the flexibility of VLOOKUP and HLOOKUP to perform lookup functions across different sheets, providing a seamless way to access and analyze data from separate locations.', 'The function of VLOOKUP and HLOOKUP is explained, including specifying the lookup value, table array, and handling range lookup. Provides a step-by-step explanation of the VLOOKUP and HLOOKUP functions, detailing the process of specifying the lookup value, table array, and managing range lookup, offering practical guidance for users.', 'Illustrates the usage of HLOOKUP against horizontally organized data, showcasing the process and differences compared to VLOOKUP. Offers a clear demonstration of using HLOOKUP against horizontally organized data, highlighting its similarities and differences compared to VLOOKUP, providing a comprehensive understanding of both lookup functions.']}, {'end': 1307.849, 'start': 1046.54, 'title': 'Using xlookup in excel', 'summary': 'Discusses the limitations of using horizontal tables in excel, the advantages of using xlookup over vlookup and hlookup, and demonstrates how to use xlookup to efficiently retrieve data without rearranging the table, showcasing its intuitive functionality and built-in error handling.', 'duration': 261.309, 'highlights': ['XLOOKUP can do everything that VLOOKUP and HLOOKUP can do, and more, including searching on vertical and horizontal lists without the need for separate functions. XLOOKUP is a versatile function that offers more capabilities than VLOOKUP and HLOOKUP, allowing it to search on both vertical and horizontal lists without requiring separate functions for each type of lookup.', 'Demonstrates using XLOOKUP to efficiently retrieve data without rearranging the table, showcasing its intuitive functionality and built-in error handling. The demonstration shows how XLOOKUP can efficiently retrieve data without the need to rearrange the table, thanks to its intuitive functionality and built-in error handling, making it a user-friendly option for data lookup.', 'Explains the limitations of using horizontal tables in Excel and the necessity of using absolute references for proper table lookup. The explanation highlights the limitations of horizontal tables in Excel and the importance of using absolute references for proper table lookup, addressing the challenges faced when organizing data in Excel.']}], 'duration': 428.758, 'thumbnail': '', 'highlights': ['XLOOKUP can do everything that VLOOKUP and HLOOKUP can do, and more, including searching on vertical and horizontal lists without the need for separate functions.', 'Demonstrates using XLOOKUP to efficiently retrieve data without rearranging the table, showcasing its intuitive functionality and built-in error handling.', 'VLOOKUP and HLOOKUP can be used across different sheets, allowing for lookup between separate sheets.']}, {'end': 1914.639, 'segs': [{'end': 1622.775, 'src': 'embed', 'start': 1578.293, 'weight': 0, 'content': [{'end': 1585.236, 'text': "and here you'll see then that it will automatically look up both the revenue and the cost for each individual cookie.", 'start': 1578.293, 'duration': 6.943}, {'end': 1589.659, 'text': 'So this works exactly how I want it to, but this is yet another benefit of XLOOKUP.', 'start': 1585.277, 'duration': 4.382}, {'end': 1594.241, 'text': 'Now you might be thinking XLOOKUP is pretty powerful and this is a.', 'start': 1590.419, 'duration': 3.822}, {'end': 1598.343, 'text': 'I should probably be using this over VLOOKUP and HLOOKUP, but wait, there is more.', 'start': 1594.241, 'duration': 4.102}, {'end': 1604.106, 'text': "Let's click into the next sheet called XLOOKUP wildcard match and let's see how this works.", 'start': 1598.723, 'duration': 5.383}, {'end': 1611.049, 'text': "Within XLOOKUP, there's an option called match mode and here you can see all of the different options.", 'start': 1604.666, 'duration': 6.383}, {'end': 1619.454, 'text': 'Now in VLOOKUP, we were able to do an exact match and we were also able to do an exact match, but if none was found to return a smaller item.', 'start': 1611.33, 'duration': 8.124}, {'end': 1622.775, 'text': 'With XLOOKUP, we have even more control.', 'start': 1620.094, 'duration': 2.681}], 'summary': 'Xlookup offers more control and options than vlookup and hlookup.', 'duration': 44.482, 'max_score': 1578.293, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw1578293.jpg'}, {'end': 1781.57, 'src': 'embed', 'start': 1753.395, 'weight': 2, 'content': [{'end': 1758.758, 'text': 'But the nice thing is with XLOOKUP, I have access to these different search modes.', 'start': 1753.395, 'duration': 5.363}, {'end': 1764.841, 'text': 'So here, just like with VLOOKUP, I could just start at the top and then work my way down.', 'start': 1759.318, 'duration': 5.523}, {'end': 1771.444, 'text': 'but now I can also start from the bottom and work my way up if I enter negative one in for the search mode.', 'start': 1764.841, 'duration': 6.603}, {'end': 1777.648, 'text': "I also have binary search as an option and I can enter two or negative two, and it's the same concept.", 'start': 1772.165, 'duration': 5.483}, {'end': 1781.57, 'text': 'I could start from the top and work my way down or start from the bottom and work my way up.', 'start': 1777.968, 'duration': 3.602}], 'summary': 'Xlookup offers different search modes like top-down, bottom-up, and binary search.', 'duration': 28.175, 'max_score': 1753.395, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw1753395.jpg'}, {'end': 1886.45, 'src': 'embed', 'start': 1854.694, 'weight': 3, 'content': [{'end': 1856.195, 'text': 'And here I can confirm that.', 'start': 1854.694, 'duration': 1.501}, {'end': 1860.259, 'text': 'If I go down the list, we see Lola and her last order was on 7-13.', 'start': 1856.436, 'duration': 3.823}, {'end': 1864.263, 'text': 'And that now wraps up all of the functionality of XLOOKUP.', 'start': 1860.58, 'duration': 3.683}, {'end': 1870.111, 'text': "And so hopefully now, by going through these different examples, you're starting to realize the power of using XLOOKUP,", 'start': 1864.383, 'duration': 5.728}, {'end': 1873.135, 'text': 'especially compared to VLOOKUP and also HLOOKUP.', 'start': 1870.111, 'duration': 3.024}, {'end': 1879.604, 'text': "In fact, there's really not much of a reason to even still use VLOOKUP or HLOOKUP when you have XLOOKUP.", 'start': 1873.516, 'duration': 6.088}, {'end': 1886.45, 'text': 'It can do everything that both VLOOKUP and HLOOKUP can do, but it has even more power and customizability.', 'start': 1879.744, 'duration': 6.706}], 'summary': 'Xlookup surpasses vlookup and hlookup with more power and customizability.', 'duration': 31.756, 'max_score': 1854.694, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw1854694.jpg'}], 'start': 1308.569, 'title': 'Xlookup function and benefits', 'summary': "Illustrates the xlookup function, showcasing its capability to return values, handle multiple values, and use match mode to find exact or wildcard matches, offering more control than vlookup and hlookup. it also discusses the benefits of xlookup, including defining search modes and its superiority over vlookup and hlookup, with a demonstration of finding lola's last order using xlookup.", 'chapters': [{'end': 1709.262, 'start': 1308.569, 'title': 'Xlookup function explained', 'summary': 'Illustrates the xlookup function, showcasing its capability to return values, handle multiple values, and use match mode to find exact or wildcard matches, offering more control than vlookup and hlookup.', 'duration': 400.693, 'highlights': ['XLOOKUP returns revenue per cookie and cost per cookie, offering more power and flexibility compared to VLOOKUP. XLOOKUP returns revenue per cookie, such as $5 for chocolate chip, and cost per cookie, demonstrating more power and flexibility than VLOOKUP.', 'XLOOKUP allows the combination of functions to calculate profit per cookie, showcasing the additional power of XLOOKUP. XLOOKUP allows the combination of functions to calculate profit per cookie, demonstrating the additional power of XLOOKUP in performing complex calculations.', "XLOOKUP's capability to return multiple values in one formula simplifies data retrieval and manipulation, demonstrating its efficiency. XLOOKUP's capability to return multiple values in one formula simplifies data retrieval and manipulation, showcasing its efficiency in handling complex data.", "XLOOKUP's match mode provides more control than VLOOKUP, offering options for exact matches, next larger item, and wildcard matches. XLOOKUP's match mode provides more control than VLOOKUP, offering options for exact matches, next larger item, and wildcard matches, illustrating its superiority over VLOOKUP."]}, {'end': 1914.639, 'start': 1709.302, 'title': 'Xlookup benefits and search modes', 'summary': "Discusses the benefits of xlookup, including the ability to define search modes such as starting from the top or bottom, using binary search, and its superiority over vlookup and hlookup, with a demonstration of finding lola's last order using xlookup.", 'duration': 205.337, 'highlights': ['XLOOKUP allows defining search modes such as starting from the top or bottom, using binary search, and sorting the data in ascending or descending order, providing more power and customizability compared to VLOOKUP and HLOOKUP. XLOOKUP offers various search modes including starting from the top or bottom, using binary search, and sorting the data in ascending or descending order, making it more powerful and customizable than VLOOKUP and HLOOKUP.', "Demonstration of using XLOOKUP to find Lola's last order, which was on 7-13, showcasing the practical application of XLOOKUP in retrieving specific data. A practical demonstration of using XLOOKUP to find Lola's last order, which was on 7-13, illustrating the practical application of XLOOKUP in retrieving specific data.", 'Emphasizing the superiority of XLOOKUP over VLOOKUP and HLOOKUP, stating that there is little reason to use the latter when XLOOKUP offers more power and capabilities. Highlighting the superiority of XLOOKUP over VLOOKUP and HLOOKUP, emphasizing that there is little reason to use the latter when XLOOKUP offers more power and capabilities.']}], 'duration': 606.07, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/DZEPA9UhLBw/pics/DZEPA9UhLBw1308569.jpg', 'highlights': ['XLOOKUP returns revenue per cookie and cost per cookie, offering more power and flexibility compared to VLOOKUP.', "XLOOKUP's match mode provides more control than VLOOKUP, offering options for exact matches, next larger item, and wildcard matches.", 'XLOOKUP allows defining search modes such as starting from the top or bottom, using binary search, and sorting the data in ascending or descending order, providing more power and customizability compared to VLOOKUP and HLOOKUP.', "Demonstration of using XLOOKUP to find Lola's last order, which was on 7-13, showcasing the practical application of XLOOKUP in retrieving specific data."]}], 'highlights': ['XLOOKUP can do everything that VLOOKUP and HLOOKUP can do, and more, including searching on vertical and horizontal lists without the need for separate functions.', 'XLOOKUP returns revenue per cookie and cost per cookie, offering more power and flexibility compared to VLOOKUP.', "XLOOKUP's match mode provides more control than VLOOKUP, offering options for exact matches, next larger item, and wildcard matches.", 'XLOOKUP allows defining search modes such as starting from the top or bottom, using binary search, and sorting the data in ascending or descending order, providing more power and customizability compared to VLOOKUP and HLOOKUP.', 'The chapter delves into the in-depth usage of VLOOKUP, introduces HLOOKUP for horizontal list lookup, and highlights XLOOKUP as an improved alternative.', 'The importance of creating tables in Excel before using VLOOKUP is emphasized due to the ease, error avoidance, and automatic data inclusion it provides.', 'VLOOKUP can be utilized to merge separate tables of information, showcasing its versatility and practical applications.', 'The importance of organizing data for VLOOKUP, including ensuring the lookup value is in the leftmost column and in ascending order, and having a common field for connection.', "Demonstration of using XLOOKUP to find Lola's last order, which was on 7-13, showcasing the practical application of XLOOKUP in retrieving specific data.", "The process of converting data into a table in Excel is detailed, including selecting the data, using the 'insert' option, and checking the 'table has headers' box."]}