in this video i'm going to show you how we can find the last not empty row column or value
in excel so in this particular case see we have this column and if we wanted to find
the last non-empty of row that would be this row 16. we don't want to get any of
these empties in the middle just this row 16 right there so to do that we can use
x match so i'm going to do equals x match and in x match i'm going to provide a lookup value
code star for now and quote do a comma after that and then i'm gonna provide
this column a to search now if you didn't want to do the whole column you can do
a particular range like this it's really up to you in this example i'm gonna do the whole column but
you can also do it within a range if you wanted to so i'll do the whole column a and then i'm going
to do a comma now when i get to this third option i'm going to choose this wildcard character match
comma and then after this we have this mode to search last to first because we want to go
down up to find this so i'm going to do last to first and finally close parentheses and hit enter
so you can see what i get so as you can see i got 16. now in the event when you have
for example some cells here that are formulas but they return blank so for example i can say
equals double quotes here so it's kind of like a blank cell but this cell is no longer really blank
so in this case c this would get us 17. now if you wanted this formula to still return
16 what you could do you could go here after the star and add question mark star
and in this case basically we're saying we want at least one character before we do a match by
doing that question mark in the middle and if i hit enter you'll see we get a 16 even though
in this row 17 we have this formula that returns the blank now if i didn't want the row number
but i wanted the actual value then i could do it using x lookup now you could do index and use the
16 in that that should be just valid too but i'm gonna use x lookup to just do it in one formula
i'll do x lookup and again as a lookup value i'm gonna go ahead and select that same star
question mark star and again the same logic applies here if you want the actual non-blank
cell you would just do star comma and then as a lookup array i'm going to select that column a
comma and when i get to return array i'm going to select that same column a comma and then we have
if not found in this case i'm just going to go with double quotes just to return blank basically
if everything is blank comma and then again wild card character match number two comma and finally
search last to first which is this negative one option close parenthesis hit enter and
you should be able to see how i get this last option if i delete this one from here you'll
see it will match this one so if i undo this it will move to this and if i just type hello here
you'll see how it grabs that hello so you'll see this updates to 19 now too so this is the
row number this is the value itself these formulas would be okay if you're working with text values
but you may find some weird things if you actually have some numbers so if i go ahead and for example
enter a number here you'll see the last one still returns 19 and hello and the reason for that is
because this match in our x match and x lookup actually will match text only so what i can do to
fix this problem i can just take this range and just wrap it within this function value to text
and close parenthesis around this and if i had entered now see now it returns 21 which is
probably what you would want and that same thing i could do with this so i can take this first one
we don't have to do the second one we just have to do this first lookup array value to text just
like that for that range and hit enter you'll see that would give me this if i didn't have a number
it's still going to work and it's going to return the correct thing
so there you go a couple of different formulas these same formulas should work for a row
as well so if you wanted to find let's say in this row three the last non-empty value
which would be the six you would go here and do equals x lookup
star question mark star in quotes comma and then as a lookup array i'm gonna do value to text
and then i'm gonna select this row three close parentheses comma and then return array is gonna
be that same row three comma if not found i'll just say return blank double quote comma match
wildcard characters comma and finally we want search last to first negative one option if i hit
enter see that return six because that's pretty much the last non-empty in this if i had another
number or text or whatever here that should return that value as you can see for these formulas by
the way if you wanted to find the first one you can do that one two so all you would have to do
is just switch this formula this last option to do search first to last so if i do that see it will
give me this first one six and if i do negative one well you see where this is going it's gonna
find the last one and then if you wanted to find the column number you would just use x match so x
match again the same star question mark star in codes comma lookup array would be value to text
and then this whole row over here close parenthesis and then comma and then i'm gonna do
wildcard characters comma and then again last to first negative one option close parenthesis
hit enter and as you can see it says this is column seven so it counts one two three four
five six seven that's correct and if you wanted the first one same way you could just do one here
and that's column number three right here the first non-empty cell
and there it is thanks for watching please subscribe and i'll see in the next video