Locked lesson.

Upgrade

About this lesson

Illustration and issues with the LOOKUP function continued.

Exercise files

Download this lesson’s related exercise files.

LOOKUP Part 2.xlsx
13.6 KB LOOKUP Part 2 - Solution.xlsx
13.7 KB

Quick reference

LOOKUP Part 2

Discover how to use LOOKUP function in a formula.

When to use

The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the same array.

Instructions

  • The syntax for LOOKUP is as follows:
    • LOOKUP(Lookup_value,Array
  • Lookup_value is the value that LOOKUP searches for in an array. The Lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value
  • Array is the range of cells that contains text, numbers, or logical values that you want to compare with Lookup_value.

Example

  • In the example above we want to return the value of Net Assets from Row 9 in Row 18. To do this a LOOKUP function can be used as follows: =LOOKUP(D$14,$D$5:$U$5,$D$9:$U$9)
  • The lookup_value is cell ‘D14’ which is the date we want to search for in the lookup_vector
  • The lookup_vector is row D5:U5 which is where we want to find the date (cell ‘D14’)
  • The result_vector is row D9:U9 – this will search for the last value in the row and return that value (cell ‘F9’ - $1,200.)
Login to download
  • 00:04 Continuing on with my lookup examples from last time.
  • 00:08 So last time out, by inserting rows in the actual table between rows 4 and
  • 00:13 5, I showed how the LOOKUP function and
  • 00:17 its array syntax just falls over, and therefore is not recommended.
  • 00:22 The beauty of using the vector form, which is what I've go here in this example
  • 00:27 at the moment, in cell C9, I'm currently clicked on, is that these two vectors.
  • 00:35 Could be anywhere.
  • 00:37 They don't have to be here, and here.
  • 00:41 They could be on different worksheets.
  • 00:44 One could be going across a row.
  • 00:46 One could actually be going down a column.
  • 00:50 As long as it's got five items in the list.
  • 00:53 See, bulletproof, LOOKUP just goes through.
  • 00:57 If it can't find a year, it uses the biggest value
  • 01:02 available, except well let's put 2016 in.
  • 01:07 Rats.
  • 01:09 Look at this looking for the largest value less than or
  • 01:12 equal to what you're looking for.
  • 01:14 That's why it needs to be ascending order.
  • 01:17 So that didn't quite work, drat.
  • 01:21 But it's not too difficult to sort it out.
  • 01:24 To make it bulletproof, set an example, here's one I prepared earlier.
  • 01:31 Notice, that is the formula we just created in the last time out.
  • 01:36 What I have just done is, I put an if statement in here.
  • 01:39 And let me recreate this.
  • 01:40 So, let me take it back to what it was.
  • 01:46 That was, what we had calculated before.
  • 01:54 So LOOKUP 2017 in this row and
  • 01:58 return the value and it gives me a value of 3%.
  • 02:04 What I need to do though is say what happens if this year is less than 2017?
  • 02:09 So I just put in front of the if function we talked about before
  • 02:15 If this value here is strictly less than that value, then use that value.
  • 02:24 Otherwise, do a LOOKUP.
  • 02:28 And that's it.
  • 02:29 That's it so long it's bulletproof now,
  • 02:34 got 3% up to 7%, piece of cake.
  • 02:38 Now, why am I so raving on about LOOKUP when I didn't like V LOOKUP and H LOOKUP?
  • 02:43 Let me show you with a third example.
  • 02:47 This is what happens in real life, when you're going to do financial modeling,
  • 02:50 we may have a monthly model.
  • 02:51 What will often happen when your building a model is often management want,
  • 02:56 for the first couple years or so granularity to be more detailed.
  • 02:59 So you might do it monthly for the first two years, then quarterly for
  • 03:03 the next couple years, and then annually thereafter, or
  • 03:06 some sort of variation on that.
  • 03:07 And people model it this way.
  • 03:09 Crazy, it's not consistent.
  • 03:11 You're gonna have horrendous formula saying that if it's monthly do this,
  • 03:15 if it's quarterly do that.
  • 03:17 If it's the period where it goes from quarterly, to monthly, to annually,
  • 03:20 or whatever the heck it's going to do, then you need to do this.
  • 03:23 And the formula will change and, my God, this formula's five lines long.
  • 03:27 Don't do that, instead, what you do is you go to the lowest common denominator.
  • 03:33 So if you've got monthly, quarterly, annually it's monthly.
  • 03:36 If it's quarterly and annually it's quarterly, etc.
  • 03:40 And then you group them.
  • 03:42 So here I've got my monthly modeling for sales and net assets.
  • 03:47 So this is for the income statement and
  • 03:49 has to be reported monthly whereas net assets is shown as at a point in time.
  • 03:54 So this one totals.
  • 03:56 This one is at a point in time.
  • 03:58 So obviously January, February, March are in the March quarter end.
  • 04:01 April, May and June are in the June quarter end.
  • 04:04 Don't worry about this horrible formula up here.
  • 04:06 It's just to keep the dates so that when you open whenever you open it,
  • 04:10 the date in here will be the year that you open it.
  • 04:12 You see?
  • 04:13 That's all it's doing.
  • 04:15 So how can I actually total of the sales for the March quarter?
  • 04:20 Or I use a SUMIF.
  • 04:21 Let's just do that.
  • 04:22 Learn from somebody before.
  • 04:24 SUMIF.
  • 04:26 I'm gonna look through this data here.
  • 04:30 That absolute.
  • 04:31 I'm looking for the period.
  • 04:34 March 17, and I want you to return sales
  • 04:39 figure please, making that absolute.
  • 04:44 600, 1,500.
  • 04:51 All the way up to 5,100.
  • 04:57 Now, for the net assets it actually has to be at a point in time.
  • 05:02 So I want the last value.
  • 05:03 I don't want to end up these three values 1,000, 1,101, 1,200.
  • 05:08 I want to actually take the final period.
  • 05:10 And this is where LOOKUP comes in.
  • 05:12 It finds the largest value less than or equal to what you're looking for.
  • 05:18 So if you've got duplicates, it'll take the final duplicate.
  • 05:21 It'll take the one most to the right, and that's what we want.
  • 05:25 So we just goes =lookup find me this date in
  • 05:30 this block of data, make that absolute,
  • 05:36 and return the corresponding value from here.
  • 05:42 1,200, it picks the final period of the quarter, and
  • 05:47 it goes to 1,500 1,800 etc, etc, and that's where it's useful.
  • 05:53 If you are looking for balance sheet items where you're summarising, you don't want
  • 05:58 to add things up, you want to look up values, LOOKUP is the function to use.
  • 06:03 Get off my soap box now, let's move on.

Lesson notes are only available for subscribers.

Gift this course