LOOKUP

Quick reference

• 00:04 I have made my disdain of the functions VLOOKUP and HLOOKUP quite clear.
• 00:09 I've shown you that I much prefer index and
• 00:13 match as alternatives to VLOOKUP and HLOOKUP.
• 00:16 What you may have noticed I didn't mention anything about the LOOKUP function.
• 00:21 And that's because the LOOKUP function is useful and
• 00:25 it does have a place in financial modeling and it is one of my dirty dozen.
• 00:32 LIke index, there are two types of LOOKUP function.
• 00:35 There's the vector form and the array form.
• 00:38 Now just to recall, a vector is something that is one row or one column,
• 00:43 where as an array is something that has at least two rows, and at least two columns.
• 00:48 And you may hear the term Array Formula.
• 00:52 An Array Formula is a formula you write in Excel but you don't press Enter once
• 00:57 you've finished it off, you actually press Ctrl+Shift+Enter.
• 01:01 LOOKUP does not require that sort of entering,
• 01:04 you just type in Enter in the usual way.
• 01:09 So in it's vector form, it can only have a range of one row or one column for
• 01:14 both the lookup vector and the result vector.
• 01:16 So you see, third bullet point down, the syntax is LOOKUP find me a lookup
• 01:21 value in a lookup vector and then give me the result in the result vector.
• 01:27 A lookup_value can be a number, it could be text, a logical value.
• 01:30 So, the lookup_vector can only contain one row or
• 01:34 one column and so must the result_vector.
• 01:38 And the two must be the same number of rows or columns.
• 01:42 It's got to be the same size.
• 01:46 Another thing, lookup vector must be placed in ascending order,
• 01:51 otherwise it won't get a correct value.
• 01:52 Now, notice it's ascending.
• 01:54 I didn't say strict ascending, you can go 1, 1, 2, 2, 3, 3, it's fine.
• 02:00 Notice, it's not case sensitive, uppercase and lowercase are equivalent.
• 02:05 And if you can't find the lookup_value it fractures the largest value in
• 02:08 lookup_vector that is less than or equal to lookup_value.
• 02:11 So, it's a bit like the match function.
• 02:14 When it's match type one.
• 02:15 And if it can't find the value, it gives N/A for not available instead.
• 02:21 In it's array form, Microsoft states that the look of the array form is
• 02:25 provided purely for compatibility with other spreadsheet programs.
• 02:29 And you know what, I'm up for that.
• 02:31 I actually think it's a bit too confusing otherwise, and it's a bit dangerous.
• 02:35 Essentially you have a block of data and it will lookup a value in the first row
• 02:40 and return it in the last column of the block, unless there's too many rows.
• 02:47 And then it goes first column and last column.
• 02:49 And that inconsistency kills the array form, for me.
• 02:52 Let me show you some Excel examples.
• 02:56 LOOKUP does really have a place in financial modeling.
• 03:00 It does have the restriction that the LOOKUP data has to be in ascending order.
• 03:05 It doesn't have to be straight, but, it has to be in ascending order.
• 03:10 And where I think you should really use it, it's when you're looking at dates.
• 03:15 That's it's key calling,
• 03:17 I think you can call it if you want a better phrase in Excel.
• 03:20 Let's take a look at this example.
• 03:23 Here in rows four and five, I'm looking at my consumer price index,
• 03:28 my CPI for what it will be each year.
• 03:32 Now, in a model sometimes you must have lots and lots and lots of periods.
• 03:36 It's not just enough to just have five periods.
• 03:38 You might just have to be forecasting monthly for the next ten years,
• 03:41 which would be 120 columns for example.
• 03:44 You don't want people to have to put inputs in 120 columns.
• 03:48 They'll go stir crazy.
• 03:50 You've gotta think of the end user occasionally, you know.
• 03:53 So, there's a way around it.
• 03:55 What you can do is, when you realize that your assumptions will reach a steady
• 03:59 state, i.e., where the values will just keep being the same thing thereafter,
• 04:04 then you can just say, okay, from this period on, what is it?
• 04:07 And that's what I've done here.
• 04:08 In this little block here, I've got 2017 to 2021 onwards.
• 04:12 So after 2021, it's just gonna use 7% each period.
• 04:18 Now the trick here is, these formulas are actually giving me a year.
• 04:25 And notice that is actually a number.
• 04:28 If I go Ctrl+1 to format cells, do you see I've used custom number formatting to make
• 04:34 it look like the Word 2021 onwards, whereas in fact it's just using 2021.
• 04:39 Just a trick with custom number format.
• 04:43 So down here,
• 04:44 I actually want see what the CPIs is about to use in my model each period.
• 04:48 And I'll use the LOOKUP.
• 04:50 So I want to use the array form first, and then show you why we don't do that.
• 04:55 If I use the array form, I just have to go LOOKUP,
• 04:59 find me 2017 in this block of data.
• 05:03 And that's it.
• 05:05 It's just as easy as that.
• 05:07 I just need to make sure that this stays absolute, so
• 05:09 I'm gonna copy it across to go find.
• 05:11 And then that perhaps links to row eight by pressing the F4 function key.
• 05:16 But that is it, 3%, done.
• 05:19 And if I copy that across.
• 05:22 You'll see it's 4% in 2018, 5% in 2019, but from 2021 onwards it's 7%.
• 05:28 This is what LOOKUP in array form does.
• 05:32 It looks up this value in the first row of the array, and
• 05:36 it returns this value from the bottom row of the array,
• 05:41 as long as there's more columns than rows.
• 05:46 If that doesn't hold, let's insert,
• 05:51 Then it all swaps around, and then its formula goes wrong.
• 05:56 And that's the problem with the array version.
• 05:58 It's too unreliable in case people accidentally put things in.
• 06:02 So instead, what I'm going to do
• 06:05 is I'm going to actually use the non-array form, the vector form.
• 06:10 So I will go =lookup[ find this in this row vector and
• 06:17 return the corresponding value in this column vector.
• 06:30 Done, and that's the one I recommend.
• 06:34 Now there's more to consider with LOOKUP, I'll be looking at that next time.

Lesson notes are only available for subscribers.