all right so in this video let's talk about the fact that X lookup function returns a reference
and not a value when you do a lookup and why that could be important so everything we talk about
here is going to apply both to Excel and Google Sheets and I'm going to assume you already know
how to do xlookups if you don't I'm going to link to a video Below in the description where you can
watch and understand how xlookup works but right now let's just go ahead and do a quick example
here so if I for example go ahead and select a start year here 2016. and I do an axle lookup
so I'm going to search for this comma in this column comma and in this return column I'm going
to get my results close parenthesis hit enter see I get 74 160 which is basically this matching
value for this year now the part that I want to go over here is that when we do this x lookup
what it does return is not the value of 74 160 but the reference to that cell and the reference
to the cell is B3 so essentially that X lookup returns B3 and when we do equals B3 we will get
that amount now why is this important so let's actually choose something here too so imagine if I
had to get the total from here through here right a basic sum function so we go from B2 through B9
and we use the colon as our range syntax and if I enter that should get me the total of all of that
if I wanted this to start from say B3 I could have entered B3 here right so to start from B3
go to B9 I get my total now if you remember when we do this x lookup it doesn't return the value
it Returns the reference so this returns that B3 which means I could copy this without equal sign
go back to this formula and replace this B3 with this axle cup as a starting point in this range
and with this if I enter this still works except that now if I update this 2016 to say 2015 see
this total update so now it's going to start from here and go until here and if I update it
to 2017 again this total updates automatically so now instead of having a hard-coded starting
point for a range we actually have a lookup that finds the reference and I could do the
same with this part right instead of hard coding B9 we could have let me just copy this formula
and paste it here and here I'm going to change this B13 to this cell b14 which points to this
year 2021 and that should get us the reference to this cell right so again I could copy this
go back to here and replace this B9 paste the reference hit enter and now I'm going to get
the total between this and this between 2017 and 2021 if I update this to 2022
this will update if I go to 2020 see it updates again so now we can get starting from here
through here basically using our references and
the same exact thing works the same way if I copy this and paste this in Excel
see the same thing so we got our total and if I play around with my start and end
that should give me the total just to check let's make sure that we just do the total of these
so you should be able to see we get the same amount so it works just fine
so there we go we got a reference using xlookup and we use that reference in a
different function now I did this example with sum function but we could have used
this in any other function right so to give an example like this lookup if I just do this
this is gonna search for this year 2017 in this column and it's going to go find this reference
where this number 62 000 is this one now that's essentially like B4 so if you imagine if we do
something like an offset function offset accepts a reference as our first argument so for example
I could say this cell comma and then how many rows I want to offset from this and how many
columns I want to offset from this so for example if I do raw offset 0 and column offset minus one
see essentially that went the same Row one column left to this cell 2017 and yet again instead of
having this before here I could just go here copy this x lookup go back and instead of B4 I'm gonna
do an X lookup which returns a reference which means now my X lookup as you can see it's gonna
find this year 2017 here then it's going to return this reference to this B4 and then our offset it's
going to take that before and go same Row one column left or maybe we want to go one row down
and the same column or something like that right like this one and zero so now it's gonna go see
2017 was here and we did offset of one row so it's going to go this next line which would be the 68
000. there you have it and again if I update this to a different year now 2020 again our X lookup is
going to find a reference to this then our offset is going one down so it takes to the cell and
this should also work again in Google Sheets the same exact way so if I go back to my sheets
paste it here now it's not the same number because this value is not the same so in Excel
this was 2020. if I go back and update that too 2020 then we'll see how this
not this one but this see it's going to go find 2020 that's going to return this 84 000 number
reference so again it's not going to return 84 000 it returns B seven right and from that B7
we offset one row down and we end up with the 71 000. so you can see that reference in action
right here as well as well as in this formula if you like my content make sure you like
and comment subscribe to the channel thanks for watching and I'll see in the next video