Are it sort of got a beautiful V lookup formula written it's populating data properly.
We've even wrapped it in a fair statement to account for the missing values.
All is good.
I want to talk about one other tweak that we can make to the table array and two other approaches that
we can use which can be beneficial in certain situations.
So right now we've written our Look up with a fixed table array.
So right now look up is looking for the lookup value within the array from A1 through H.
Twelve thousand fifty six.
And exactly that array.
There are two other approaches we could take.
The first is to use a named array.
So if you recall in one of the early lectures in this section we created a named array based on our
fielding data.
But if you recall when we created it we hadn't defined this key column A yet.
So if you actually select B 1 through column H and control shift down there there's my fielding array
.
So it basically shifted my name to array over.
And in this case my lookup array needs to include Column A because that's where my lookup values are
going to live.
So what I can do is go into the formulas tab.
Select Name Manager.
Here's my fielding array that we'd created.
I'm going to edit that or I'm going to do is change the be one delete to be changed into a hit.
OK.
When I close that.
Now if I select a one control shift arrow right arrow down you can see that it's defined as my fielding
named array.
So now that we've made that little tweak I can go back to the look up and just jump into the table array
piece in my formula.
Delete the entire thing and just write Fielding.
And as you can see it will apply all the same values.
I can do the same thing here again.
I'm going to delete the entire table array starting with the tab name fielding data followed by the
exclamation point.
Leave all of that in just the right field.
And again Excel will recognize that as a named reference and it will know exactly what reference that
refers to and populate the correct errors.
So I'm just going through columns q r s t and just making the same update here.
In every case note that I'm not putting fielding in quotes or anything because it's not a text string
it's a reference to a named array.
And as you can see Excel is popping up this little box because as I start typing fielding it's searching
through the name manager and seeing if I've created any named arrays with that name.
And so in fact it does find it.
So I can just give it a click and I'll drop it in and we're good to go.
So I can go ahead and apply all those changes I just made down.
And once this finishes calculating you'll see that it's fielding in all cases.
So you can see it's a little bit more compact and a little bit of a cleaner formula.
The problem that I have with named arrays is that a I need to remember that I've created this name for
it and use the name manager to check exactly which cells that named array refers to.
The second is that it's it's fixed so if I need to modify or add data I'm going to need to added the
fielding reference to account for that.
So one last option for the table array which I actually prefer and I use quite a bit is rather than
using and named a named array and rather than using a fixed specific array like a 1 through H whatever
it was what I'm going to do is select fielding data.
Note that I'm in the table are a piece of my formula right now.
Select fielding data just click all of column may hold down shift and all of column H and hit enter
and apply that down.
So same exact result.
You can see now it's looking up.
Basically any rows and columns A through H not just down to twelve thousand fifty six.
And the benefit of doing this is that.
Now if I get an additional year of data or if I refresh the data in my fielding data tab I won't have
to touch those Vlookups I want to touch the reference and I want have to touch the name manager because
every time it performs the look up it's going to search every populated row from column A through H
.
So it's a it's a more effective more efficient way to use lookup functions when you have a dynamically
constantly changing or updating lookup reference table.
So that's my little tip.
I use it quite a bit and I'll just show you the same exact thing for the last four columns just so that
you're comfortable with it.
A through H enter and again this just seems to save me quite a bit of time so that I don't have to remember
you know all these nuances about how I have defined certain named arrays or exactly what my lookup table
is currently defined as in terms of the cell reference.
I just always know that no matter what my lookups are going to work properly so the supply goes down
.
You know take a minute to calculate and they go.
So you see that nothing's changed on the surface because all three methods of writing this formula that
have shown you do the exact same thing.
It's just that they all have very specific benefits and drawbacks so there you go.
This is what I would recommend using just total column references as opposed to named arrays or fixed
arrays so that you're gonna have a really really efficient lookup function