1 00:00:00,05 --> 00:00:01,06 - [Narrator] You may have heard of a function 2 00:00:01,06 --> 00:00:03,04 called XLOOKUP. 3 00:00:03,04 --> 00:00:06,04 We're going to build an XLOOKUP formula right now. 4 00:00:06,04 --> 00:00:09,00 XLOOKUP enables you to retrieve values 5 00:00:09,00 --> 00:00:11,09 based on a LOOKUP value in the same row. 6 00:00:11,09 --> 00:00:15,01 So for this example, we're going to look up a specific state 7 00:00:15,01 --> 00:00:18,04 in column E and retrieve the status of that state 8 00:00:18,04 --> 00:00:20,02 from column F. 9 00:00:20,02 --> 00:00:23,06 So right here in cell I1, here's a state, 10 00:00:23,06 --> 00:00:26,01 and we can change which state we're looking for 11 00:00:26,01 --> 00:00:27,09 and it's going to return the status 12 00:00:27,09 --> 00:00:31,00 right here in cell I2. 13 00:00:31,00 --> 00:00:33,03 Now I know it may seem like I'm bringing out something 14 00:00:33,03 --> 00:00:37,00 really complex after we've only done a few SUM formulas 15 00:00:37,00 --> 00:00:40,07 using AutoSum, but the first point is that XLOOKUP 16 00:00:40,07 --> 00:00:43,01 is really powerful and you'll be so glad 17 00:00:43,01 --> 00:00:44,06 you know how to use it. 18 00:00:44,06 --> 00:00:48,05 Two, it is not as confusing as it may first seem. 19 00:00:48,05 --> 00:00:51,05 And three, it's just a great way for me to show you 20 00:00:51,05 --> 00:00:54,04 how to figure out how other functions work. 21 00:00:54,04 --> 00:00:57,00 For example, once we start building this, 22 00:00:57,00 --> 00:00:59,08 you'll see that it's really easy for you to figure out 23 00:00:59,08 --> 00:01:02,09 what kinds of parameters Excel is looking for 24 00:01:02,09 --> 00:01:05,00 for that specific function. 25 00:01:05,00 --> 00:01:07,04 I'll show you what I mean right now. 26 00:01:07,04 --> 00:01:10,06 My cursor is in the cell and I'm going to type my equal sign 27 00:01:10,06 --> 00:01:13,04 so that Excel knows we're creating a formula. 28 00:01:13,04 --> 00:01:20,05 And now I'll just dive right in and type XLOOKUP. 29 00:01:20,05 --> 00:01:22,02 I'll put in my open parentheses 30 00:01:22,02 --> 00:01:24,01 because that's how we start these functions. 31 00:01:24,01 --> 00:01:26,00 And this is what happens. 32 00:01:26,00 --> 00:01:30,02 The help file pops up and the bold faced item is currently 33 00:01:30,02 --> 00:01:32,08 what it's expecting for me to put in there. 34 00:01:32,08 --> 00:01:35,05 In fact, if I hover my mouse over this, 35 00:01:35,05 --> 00:01:37,00 it becomes a hyperlink. 36 00:01:37,00 --> 00:01:39,00 I can click on this and it's going to take me 37 00:01:39,00 --> 00:01:41,07 to the help file so I can further look up 38 00:01:41,07 --> 00:01:45,06 exactly what it's looking for if I'm not sure. 39 00:01:45,06 --> 00:01:48,06 In this case, it wants the LOOKUP value. 40 00:01:48,06 --> 00:01:52,05 We're going to look up the contents of cell I1, 41 00:01:52,05 --> 00:01:55,00 so I'm going to put that in there. 42 00:01:55,00 --> 00:01:57,06 And we separate the parameters with a comma, 43 00:01:57,06 --> 00:02:00,03 and as expected, now this one's boldfaced, 44 00:02:00,03 --> 00:02:03,00 it's moved on to the second parameter. 45 00:02:03,00 --> 00:02:06,02 Now it wants to LOOKUP array. 46 00:02:06,02 --> 00:02:09,08 That is what column, what data are we looking up 47 00:02:09,08 --> 00:02:11,06 that value from? 48 00:02:11,06 --> 00:02:15,00 And I can just select all of this data 49 00:02:15,00 --> 00:02:17,04 because we're looking up the states. 50 00:02:17,04 --> 00:02:19,06 I'll put in another comma, 51 00:02:19,06 --> 00:02:21,07 and the next parameter that's bold faced, 52 00:02:21,07 --> 00:02:25,06 now it wants to know what type of data to return. 53 00:02:25,06 --> 00:02:28,01 Now, once it finds the state, I want it to return 54 00:02:28,01 --> 00:02:33,06 the status, so I want it to return something from column F. 55 00:02:33,06 --> 00:02:36,09 Now, this next parameter has brackets around it, 56 00:02:36,09 --> 00:02:39,09 that tells us that it's an optional parameter, 57 00:02:39,09 --> 00:02:42,00 meaning I could be done right now, 58 00:02:42,00 --> 00:02:44,01 but in this case I want us to return 59 00:02:44,01 --> 00:02:48,07 some nice friendly text if it can't find any values. 60 00:02:48,07 --> 00:02:50,05 Because it's just regular text, 61 00:02:50,05 --> 00:02:52,03 I need to put quotes around it. 62 00:02:52,03 --> 00:02:56,06 But let's type, Not found. 63 00:02:56,06 --> 00:02:59,04 I'll put in one last optional parameter. 64 00:02:59,04 --> 00:03:03,01 It's asking if it wants to search for an exact match. 65 00:03:03,01 --> 00:03:06,06 It's even telling me what I can put as a parameter. 66 00:03:06,06 --> 00:03:09,05 In this case, it wants a zero. 67 00:03:09,05 --> 00:03:13,07 To be done, I'll put in my closed parentheses 68 00:03:13,07 --> 00:03:16,05 and I'll hit Enter and let's see what happens. 69 00:03:16,05 --> 00:03:19,04 It worked, it looked at the status of Alabama, 70 00:03:19,04 --> 00:03:22,09 and sure enough that status is active. 71 00:03:22,09 --> 00:03:24,07 Here's the great thing about using these 72 00:03:24,07 --> 00:03:26,02 formulas and functions. 73 00:03:26,02 --> 00:03:28,03 They change on the fly. 74 00:03:28,03 --> 00:03:30,00 Let's put something else in here. 75 00:03:30,00 --> 00:03:32,06 I'll put in Colorado. 76 00:03:32,06 --> 00:03:34,08 And the second I hit the Enter key, 77 00:03:34,08 --> 00:03:37,01 it's going to recalculate. 78 00:03:37,01 --> 00:03:40,02 Colorado is in fact inactive. 79 00:03:40,02 --> 00:03:42,09 Now remember, we put in that status message 80 00:03:42,09 --> 00:03:44,01 if something wasn't found. 81 00:03:44,01 --> 00:03:46,00 So let's test that out. 82 00:03:46,00 --> 00:03:48,07 I'm going to put in Apples, which is not a state, 83 00:03:48,07 --> 00:03:51,04 and should not be in this list. 84 00:03:51,04 --> 00:03:54,03 Sure enough, it's popping up with Not found, 85 00:03:54,03 --> 00:03:57,03 which is that nice friendly error message. 86 00:03:57,03 --> 00:03:59,06 So that's how you can use XLOOKUP. 87 00:03:59,06 --> 00:04:01,04 And it's also a great example of how 88 00:04:01,04 --> 00:04:03,08 you can use these other functions. 89 00:04:03,08 --> 00:04:07,04 For example, if I go over to the Formula's ribbon tab 90 00:04:07,04 --> 00:04:09,09 and hold my mouse over a function, 91 00:04:09,09 --> 00:04:12,02 here's those boldface parameters. 92 00:04:12,02 --> 00:04:15,03 It's telling me how to use it, and this is the exact same 93 00:04:15,03 --> 00:04:18,00 helper text that pops up while I'm actually 94 00:04:18,00 --> 00:04:20,00 inputting that function.