Transcripts
1. XLOOKUP Masterclass Introduction: Are you still using Lou only
to fetch the simple values? Or do you know the
function but feel limited when problem
become complex? What if I could use lookup for advanced real world scenarios
like conditional lookup, dynamic data extraction,
intelligent matching, and solving problems that normally require
multiple formulas? Hi. My name is Mave Bardach, and welcome to this Advanced
Excel Loup mastery course. This course is not
about basic lookups. It is designed for
professional who already know lookup but want to
unlock its real power. So in this course, you will learn how
to use X Lou in advanced scenarios
where formulas must handle real
world complexity. So you will see how
to use lookup with multiple conditions to return result based on
more than one row. You can create dynamic,
flexible lookup system that automatically adjust
when data grows or changes. Apply approximate or
intelligent matching for slabs, ranges, and categorize data. Combine X lookup with
functions like I let filter or logical expressions to solve complex business
and financial problems. Build clean, readable and
performance optimized formulas that works in real dashboards,
reports, and models. Every concept in this
course is explained with practical examples using
realistic datasets, not textbook drawing. You will not just
learn formulas. You will learn how to think
logically with X ocup. So by the end of this course, you will be able
to design robust, scalable and professional Excel solutions using Lookup
as a core function. So if you want to move
beyond basic uses and master advanced Excel
Loup logic with confidence, this course is for you. I will see you inside
the course. Thank you.
2. Xlookup vs Vlookup, Why every Excel User learn Xlookup: Hello, everyone. Welcome to this Advanced Excel
course on X Lookup. Before we jump into the Advanced formula and
real world automation, it is very important to understand why X
Lookup was created and why older Lou function
fail in real projects. So in this lesson, we
will clearly understand why Lookup and HLookup are
whisky in professional work. What are the biggest
limitation of VLookup, while Microsoft
introduced X Lookup and in which Excel versions
X Loup is applicable. First, we discuss why Lou
functions are important. In Excel, look up functions are used when we want to patch
data from another table. For example, find product
price using the product name, patch employee salary
using the employee ID, get DST rates using HSN codes, pull customer details into
an invoice automatically. For many years, the most
popular Lu functions was VLA. But popularity doesn't
always mean best. Let us understand this
with a real life example. Imagine you have a sales data, product code, product
name, and the price. Now your task is simple. You want the price using the
product code in this table, VLookup can do that, but problems start when
data becomes dynamic. Let's get started and learn
how we apply the VLookup. So it start from the equal sine. Lou, give the lookup value, coma give the complete table from which we want to
extract the price, coma give the column
index number. In this case, it is three. Look up always find or look up the values to
the right hand side. So here I have product
code in column A, and price in column C. So this is one, two, and
three. I will type three. Then the last optional whether we want approximate
or the exact match. So I need exact match. I will close the bracket, I will press Control Enter. By mistake, I put two, I will change it to three, then Control plus Inter. Now I will double click on here. It will automatically drag
to the last row of data. So I got my price, but problems start from here whenever
someone made changes. Now imagine someone insert a new column between
product name and the price. So I will insert one column, product name and the price, and you will see we got
problem over here because we have written the three and our complete result
gone from here. Now we need to manually
fix this error. We need to type four to get the exact result because
this is my first column, this is second, this is third, and now the price moves
to the fourth column. The result is wrong in
professional Excel files, columns are inserted,
columns are deleted, data structure keep changing. Look up is not flexible. Whenever structure change, you
must fix formula manually. This is waste of time
and increase risk. Also, it has poor readability. When someone else
opens your file and see your formula
with column numbers, they do not know which column is two and which
column is three. This makes files hard
to audit and maintain. It also has many problems, but whenever I move this
column to here, I got error. If I move this column to here, also get error, but
Excel is showing error, we are not getting
the proper results. So I'll undo this one. And now, I'll let you know why Microsoft introduced Excel Loup. So Microsoft observed how people actually use
Excel in real companies. They notice that user
need left lookup, user need Cifer formulas, user want readable formulas, user want fewer halber columns. So Microsoft created X lookup. X Loup was designed
to replace VLookup. H Loup also replace many index match formulas in a single formula with multiple
advanced capabilities. So let's understand
conceptually with X Loup, you can look up left, right, top or down. You do not use column numbers. You select lookup columns
and return columns directly. Now we will use X Lookup
to find out the price. So it will start
from the equal sign. We will type X Loup. And now you can see it
has many arguments. So are mandatory and
some are optional. So first three are mandatory
and last three are optional, but they are very important in advanced techniques which we are going to explore
incoming videos. So in this case, I'm just giving you an simple example
how to use X lookup. So first, we have to
assign lookup value. In this case, it is
product code and then coma we have lookup array. So this is my lookup array. So in VLCal first we have
to assign the lookup table. But here I'm only
assigning where my lookup array by
using that code, I want to extract the
data from this table. So in this case, it
is product code, and then I will type coma. Then the written written
array means we have to give the column for which we
want to extract or fetch. In this case, it is price. Okay? Then coma if function
could not find any formula, what action we have to do. So we are not going to
use this right now, match mode, exact match, then the smaller, larger
or wildcard, also. So in this case, I'm
going to use match case, and I don't want to
use search case. I'm going to close this
one, but before that, I want to fix
something that I want to make it constant
using the F four. Also, I have to fix this,
making it constant, and then I will press Enter, and then I will drag
to the last row. I got the same answer, but this time, let's see the magic. I will insert something here. Still, I'm getting my answer
correctly. Okay, no problem. I'm going to delete this one. Okay? I'm going to
cut and insert here. Still, I'm getting my
answer, but not here. But not here in this table
where we use Vlookup. I'm going to cut and paste
this data over here. So now my lookup array is in right side and the lookup
values are on the left side, and still I'm
getting everything. So it is completely dynamic. So it is very powerful
for advanced users. So we will learn in depth practical examples
of X Lou incoming videos, so stay tuned to the
next video. Thank you.
3. XLOOKUP Syntax: In this lesson, we
will understand the complete syntax
of lookup function. We will break down every
argument step by step so that even bigners can easily or clearly understand how X
loop works in real projects. The Lou function
has six arguments. Out of these first three
arguments are mandatory, which are written
in black glove and remaining all three are
optional but very powerful. The first argument is u values. The lookup value means what data you want
Excel to search. For example, if you find
the salary of employee ID, then the employee ID
becomes the lookup value. The lookup value
can be a number, text, or a cell reference. In real word file or
practical examples, we usually use cell references instead of typing
the value directly. The next one is lookup array. The lookup array is
the second argument. Look array means the column or ranges where Excel will
search the lookup value. For example, value. If the employee ID are
stored in column A, then the column A becomes
the lookup array. Unlike V X Lou does not require the Lou column
to be on the left side. X lookup can search
in any direction. This is the biggest
advantage of X lookup. Third argument is return array. The return array means which column Excel should
return the result, for example, if you
want to return salary, then the salary column
becomes the return array. X Lookup does not
use column numbers. Even if you insert or delete, the formula will never break. Fourth argument
is, if not found. This argument decides what Excel should display if
lookup value does not exist. Instead of showing
hash and error, you can display meaningful text like not found or invalid ID. Using this argument,
make your report neat and clean and professional. The fifth argument
is match mode. The match mode control how
Excel matches the up values. By default, lookup
use is exact match. You can also use
approximate use for slabs, ranges, grading systems. Match mode also allows
wildcard matching, which is useful when searching partial
names or the codes. Search mode. The sixth
argument is search mode. Search mood control the
direction of search. By default, Excel searches
from top to down. You can also search from bottom to top to find
the latest record. This is very useful when working with
transaction history, salary revisions,
or latest invoices. So now you understood all six argument of
X lookup function. In the next lesson,
we will apply X Lou in real life employee
salary example. So keep practicing. You will master X Lookup very quickly.
4. XLOOKUP Example: In this lecture, we will take
one practical example which you can apply in your real life. We will take the example
of an employee ID. So we have a table which
having employee ID, their name, their department,
and their salary. We have another sheet. It can be available in this sheet or it can
be in another sheet. So I'm taking the
example within this set. Later on, we will also
cover many examples where we will search from another
sheet or another workbook. So let us start. As you know, we always start with equal sign when you use
any function or formula. So we type X Lou Press tab. The first argument
is lookup value, so we can quickly give the lookup value employee
ID as a cell reference. Now coma. Second argument
is lookup array. So lookup array is the
Employee ID column. So I can give like this and press F four from the keyboard. Then coma and I want to
retrieve the salary. This is the salary column. I will select the same range of saurdy column and press
F four, and then coma. I have to define if
not found value. Sometimes, when we look up
a particular cell value, it may possible it is not
available in the lookup table. So it is best practices to
enter this value always. Let me give you one
example over here. I have employee IDs
written in the column H, and I'm going to
find out the salary. I have total five
employee IDs 101-105. But intentionally, I
have written one now. Employee ID, which is
not available over here. So let's see the impact
what we are getting. I'm not going to enter remaining three optional arguments, first, we will see
what we will get. So I will close the bracket,
I will press control. Enter, I will double
click over here, and here I can see NA. Let's say I'm calculating
their salaries, and if I get the NA, my complete salary sheet will become useless because
I will get the error, and now I cannot use this
error to calculate the salary, and I will not get
the total amount. I will get useless data. So to get rid of this
kind of problem, we have to assign,
if not found values. Always, whether it is optional, but you have to assign. So in this case, as I am
retrieving the number, and it can be used
for a calculation. So it is recommended to use
zero or any other value, which will not impact
your calculations. If it is a text,
then you can apply some text arguments in double quotation mark
like not applicable. Employee ID is not
available like this. So I'm entering zero right now. The next optional
value is exact match. So right now I'm
using exact match. I will explain all
four examples in details taking the practical
examples incoming video. I'm taking exact match
means if one, zero, one exactly match with the lookup value in
the lookup table, then only it has to
retrieve the value. Otherwise, it will show error. Then press Coma. The next
one is for searching. So right now it is searching
from top to bottom, but we will also take
practical examples to find out what is the use
of these optional arguments. So I'm closing right now
without entering this data, and this time I got zero, I will I can also
drag to the top side for copy paste the same function or the values from
bottom to top. So this is a simple
example we can use up. So in coming videos, we will
explore some more examples in details with a lot of
practical real life projects. Thanks for watching. Stay
tuned to the next video.
5. Automate Bank Account Reconciliation using XLOOKUP: Hello, everyone. Welcome to
the Excel advanced course. In this video, we
will take the example how we can use the X Lookup
for bank reconciliation. Here I have bank
statement in Excel, where I have transaction date value and the reference number, then withdrawal and the deposit amount which is in the positive. Now I have bank liture which we exported from any ERP
or accounting software. Now our task is to match the amount based
on the reference number. For this, first of
all, we have to change the numbers into positive and the negative using
the debitant credit. For this, we need
a helper column. So you can give any most
of the time in practical, I give column name as helper. So I will use I function to convert the
credit amount in negatives. That will help to compare the amount with
the bank accounts. So my formula start with
equal if and my logic would be if this cell reference
equals to credit, then the amount would be
multiplied by minus one. Otherwise, it would
be positive, same. Closing the Bagget
Control plus Enter, I will double click
and I got my answer. Also, as you can see if I go down by pressing
Control down a rookie, it is moving to the last row, but I cannot see the header. For this, I will
freeze the first row, I will go up, I will
go down by one row. I'll go to view and freepens
and the freepens option. Now if you see
whenever I go down, I can see my header, so I
know which column is it. Similarly, I will go
in the bank statement, I will select second row. After the header,
I will go to view freepens and I will select
freepens once again, and I have my header
at the top row. Here, also, I will create
one helper column. The withdrawal amount
is in positive numbers, so that also I want to
convert in negatives. So instead of doing
manually, here also, I will use if so my logic would be if deposit cell is blank. I will use another inbuilt
function called is blank. I'll give reference of
that particular cell. So if that cell is blank, then it will move to the Vidual amount
multiplied by minus one, and if value is false, then I want this value, closing the bracket
Control plus enter, and I will double click here. I got my helper values
in all the columns, now I will come on
the bank laser. First, I will check data from the bank laser to
bank statement. I need to retrieve the
amount in helper column. Then I will compare
with this value. Here I will type X look up. We have to do the same task
in bank statement as well. I need to fetch the column F data into the bank statement
and then compare. I'm doing here and
your task would be to fetch the bank laser data. X look up and I'll give
reference of this cell Coma. Lo array. Look up array, which is in the bank statement,
I will go over here. Instead of giving
a particular range because this is the
continuous data. If I want to do the bank reconciliation
of future amounts, so I can copy page. In this case, I can use the complete column
as a lookup array. Then coma and I would like to fetch column G and this time, I'm going to use if not found. I want to tell the
system that if value is not found here. I would like to get that
amount not in bank statement. I want this text or it can
be anything and the text would be always in the double quotation mark
and I need exact match. I don't want search
model right now. I'm going to closing
the bracket, Control plus Enter and I
will double click over here. I will adjust the column width by double
clicking over here. First of all, I got some sense where the
data is not matching. For this, we need to identify, so I will go to the Data
tab and then filter, click over here, and then I will deselect this and I
can click over here. These are the amounts and
data which is not matching. It means the reference number
has been written wrong, we have to check and we
have to verify and then we have to correct in bank
statement, then it would match. The second thing I want to
do is to compare the amount. Here, I will compare
or anything. I will compare simply select the helper
amount and equals to X lookup amount from
the bank statement and Control plus Enter
and double kick. Again, I'll go to the data. Filter. Again, I need to apply the filters to come to the
data tab and click on filter, and from here, I
need to deselect the two values and select
the first values early. So these are the
mismatched data, and you can rectify, you can search what is the
reason behind the differences, and then you can correct
in your books of accounts. Then again, you can
rectify over here. By that way, you can use the lookup function for bank reconciliation or
the laser reconciliation, or the two statements
using a lookup value. So incoming videos,
I will also explore some more real life
practical examples. So your task is to do the same thing with
the bank statement. You can download this workbook
from that description, and then you have to apply
the Excel look up here, then you need to
compare and you have to identify and filter the
data, which is mismatched. So thanks for watching. Stay
tuned to the next video.
6. Match Mode Argument Advanced Part 1: Hello, and welcome once again. Now let us understood one of the most powerful argument
of X lookup function, which is called match mode. Match mode controls how Excel finds the matching values
inside the lookup array. This is the main
reason why Loup is far more advanced and
reliable than V Lou. Roco function has six argument, and match mode is the fifth one. So if you do not mention match
mode during the X lookup, Excel automatically
uses the exact match. But in real life
business models, we should always understood
a control this argument. Let us start with
the match mode zero. This option means
exactly match only. Excel will look for a value that exactly matches
the lookup value. Let's start the first
one match mound zero. This option means
exact match only. Excel will look for a value that exactly matches
the lookup value. If Excel finds the value, it returns the
corresponding result. If Excel doesn't find the value, it returns an error or the text mentioned in
the if not argument. Use this option
when working with employee ID invoice number, customer code, account
number, and product ID. Means whenever we
have to find out the exact number like the
coding or a particular number, then we have to use
exact match only. This is the best, safest, most commonly used option. No shorting is required
for this mode. We have already discussed, but I'm also taking
one example over here. I have product code and their
prices written over here. I need to extract the price
using the product code. I will use lookup. Then we'll go to lookup
value, that coma. I will mention the lookup array, fix it by pressing Aor Coma. I need to mention return array, fix it with the For control. I need to mention
double quotation mark if value is not found. From here, our mode option unable and we need
to input exact mode. I already explained
what is the use of it, so I'm selecting zero, and if you will not mention, Excel will automatically
consider the exact match only, and shorting is not required. So we will not mention it
Control plus Enter and double click to drag this
function to the downside. This is how we can
use the exact match. We have already explained, but
we also learned over here. Now let us move to the
match more minus one. This option first
tries to find out an exact match if exact
value is not found. Excel returns the largest value that is smaller than
the lookup value. The lookup array must be
sorting in ascending order. This option is
used for tax labs, salary slabs, commission slabs, interest rate slabs or
discount structure. This mode is extremely
important in financial and payroll
automation and the sales commission
distribution, et cetera. Let's take the example. I have salaries and the text is applicable
based on this structure. So if the salary is more than
or equals to two AC 50,000, 2% will be applicable. If it is more than 5% and AC
50,000, 7% is applicable. I need to find out
the text over here, so we will use our calculation. So salary I have
written over here. So if we change accordingly, tax rate would be
fetched from this slam. So I will start with local and I will give the lookup value to
the salary cell reference. Look up array would
be the salary. We can fix it if we want. It is not mandatory
in this case. Now the written array would
be the tax rate column. I will fix it this as well. If not found, I would
like to mention zero. Or not applicable. But if I put any text over here, so I will get the wrong
result in the calculation. So always better to enter
zero or any other number. In this case, I'm going
to select minus one, exact match or
next smaller item. And then search
mode would be first to last and make sure your data, lookup values or the lookup arrays would be in
ascending order. Now I will close this one, I will press Control Enter, and you see it is six
lag and 5% I'm getting. So it is considering the
smaller number instead. Okay. So if I change to, let's say, one lag, so 0% will come
because it is not applicable. Now I will move to three lag so it should consider 2% only. So it is looking
for lesser number. So in this case, it
is 2% because it is in between two lac
55 lac rupees. So that is why 2% is coming. So it is searching for
the smaller number.
7. Match Mode Argument Advanced Part 2: Now let us understand
match mode one. This option also looks
for an exact match first I exact match
is not found. Excel returns the smallest value that is larger than
the lookup value. Again, the lookup array must be sorted in
ascending order. This option is useful when charges are based
on minimum limits, courier, weight slab apply, penalty threshold exist, pricing
jumps to the next level. It means wherever
we have any slab. This option may be applied. Now let us understand
with this clearly. Suppose a courier company has defined weight
slabs for pricing. Slab one, we have charges. Slab two, which is
up to three Kg we have different charges
and for 5 kilograms, we have different charges. So we need to calculate the charges according to the weight someone
enters over here. But this time function will
look up for the larger value. So let's get started.
So we will type our function X
lookup, press type, Lou value would come here, then the array
would be this one, and return array
would be the charges. Value if not found
would be zero. In this case, we
can type any text. But here, as I'm looking for a numeric value for
doing any calculation, so zero would be
a better option. This time, I'm going to select exact match or next larger item. And then next would be first to last ascending or searching
option would be one, and close the bracket
and press Enter. Now let us see how it's
getting calculated. I have written 1.5, so here is a one
because in this lab 1.5 is coming between
these two values, and it is looking
for larger value. It is coming to
the largest means up to one kg the
charges would be $10. From one, two, three,
the charges would be 15. If it is more than
three up to five Kg, the charges would be 19. It will look for
the larger value. If I change this to 4.5, so you can see 19 is coming. So this value is coming. And accordingly, we can
do the multiplication. The total charges will
coming over here. So in the next video, we will look for the
wildcard matches. So straight into the next video.
8. Match Mode Argument Advanced Part 3: So in this video,
we will discuss the fourth option
of the match mode, which is Wildcard match. So this option is used when
exact text is not available, but the required keyword is
present inside a longer text. In real business data, this situation happens
very frequently. So wildcard match allows Lookup to find a
partial text message. Excel checks whether
the lookup value exists anywhere inside the text, not just as a full exact value. This is done using
the wildcards. So we can use all wildcard
applicable in the Excel, so we are taking the
example of Astec. So Astik represent any number of character before or
after a wildcard. We can also use question mark. This represent only
one character. In the most accounting
and financial cases, the AstiC Wiltcard is used. So let me give you one example. I have a name full name of our employees has
been written over here. And their salary per
written over here. Now I need to extract their salary using
the first name only. This is just an example, but if we have two name where
the first name is matching, for example, I
have Rahul Sharma, and also I am
having Rahul Verma. So whichever coming first will be searched
by our function. Because I'm searching value from the first name Rahul
only, but it is repeating. So if it is from top to down, so whatever the value coming first will be searched
by the X lookup. But how we will use this one, I'm going to explain now. We will use X lookup with
the wildcard search option. So equal up, presstp and here, we have to give
some extra inputs. So this time, I'm going to use Etic before or after
our lookup value. But as you know that
we cannot input directly any text with
the self references, we have to use Posent sign, and if it is a text, then we have to use
double quotation mark. So we will type double
quotation mark, then Asterk we have to close
this double quotation mark for concat net or joining with another text value we
have to use percent. Then I will give the reference of this cell where our first
name has been written. Now I have to join second asterisk sign
after the wildcard. After the text reference, we have to type percent Asterc in double quotation
mark, and then close this one. Now coma. So now I will
give the Luka array. So I'll give the
reference of this cell. But as I have
merged three cells, so the range is
coming B 38 to D 45, which we need to
change to B only, so I have to remove D and B, you have to make sure your
argument must be correct. And then we have to fix this, make it constant, press F four. Then at last, we have to put
coma then the return array, return array is perfect. Press F four to make
it constant as well. C and enter, if value not
found, it would be NA. In double quotation
mark, then coma, and we have to select Wildcard Corrector match, press step, close the break it, Control plus Enter and double
click over here, and we got our answer
based on the first name. So now, I'll explain once again how this
function is working. I have full name, including first and last
name written over here, and this is per hour salary. I need to fetch using
the first name only. So what I'm telling
to the system is that I'm using Wildcard
means they have to search for a partial text in the complete text written
over here using the Wildcard. So I'm using Rahul, which is available over here. So it is just searching
Rahul within this full text, and if it is available,
then it will fetch the value
in amount column. But as I already explained, if I have multiple Rahul, let me change Rahul
Sharma once again, so you can see 55 is coming because it is
searching from top to down. In such cases, we always use employee
code instead of name, but this is just an example, so you have to take care
of this if you have repeat it values in the search array, you have to take care and take the action accordingly and change your formula
as per requirement. Thank you for watching.
We will move to the more advanced functions incoming videos and
real world examples. Stay tuned to the next
video. Thank you.
9. Match Mode Wild Card Real Life Example: In this video, we will cover one more example related
to the wildcard. So basically, I have already explained what is the use of it. So here is a practical example. Let's say we have sales
representation data and we need to assign the target for them according to their cities. I have name of the
salespersons and their cities, their achieved values of sales, but here I need to
define their targets, which is available
in another table. It may be in another
worksheet or the workbook here
we have cities, but it is also included
the country name. We can easily extract the city, but we need to use the wildcard option using
the X lookup function. So without extraction, we can also get the target
values over here. For this, we will use
the wildcard option. So we will start our
example X Lookup. So here we will select
our city in the Xu, but I will add the wildcard
with the asterix sign. It must be in the
double quotation mark. I'll close the double
quotation mark, then Coma Lou array would be the C. We need to make it
constant by pressing F, Coma, return array
would be the target. Here, also, we need to press
For to make it constant. Then not found value, also, I want to enter that no target. It also must be in the double quotation mark
as it is the text inputs. Now Coma this time, I'm going to select
Wildcard collector match, and that's all we need to select and then
close the bracket, press Control plus Inter
and double click over here. So now we can see here the
London has been written, and for London, we
have 710 as a target. So it came automatically, and I can check the Berlin. So it is in Berlin, German, and the target is 890
and 890 is coming. This is the practical
and real life example of the wildcard in
the lookup function. So in the next video, we
will cover one more example related to the lookup.
Thanks for watching.
10. Search Last to First: Hi, everyone. In this video, we will learn one more
example and optional argument in the Lou that is
called last 21. We are also covering
one practical example over here with the advanced
of using the table function. So right now I have one table
in which I have some dates, courses, and the
number of attendees. My target is to I have to
select course from here, and the latest training date and the number of attendee
must come automatically. So here, the dates are
in ascending order means the older dates are coming on top and the latest dates
are coming to the downside. These are the courses, and
the first training for Excel B six were attended on 12th, January 25, total
attendee was 38, the next one on fifth, April, and attendee was 47. I need to extract the date and the number of attendees
from the last to top. So this is the
requirement. Also, I have to convert this table into
the official type of table. For this, select NSA, come to insert and
click on table and make sure my table has header must
be enables. Click on it. It will be converted into
the official type of table, and we can give or assign
the name to that table, by default the table
one, two, three, and so on, will be
assigned by the axle. So let's say the course data, something like that, which
is meaningful for your user. And once you convert
into the official table, we can see table design
tab appear in which we have related function and
formulas and more options. So I have already explained
these in my advanced course. So if you want to
try, you can check and now I have to change
the formatting as well, so I will click over here,
and I will select this one. Okay. So now let's see
what is the difference between using any table with table formatting or
without table formatting. So this time, I'm going
to select course here, and now I will start my
example of X lookup. So I will try writing Lookup
after the eqosge then Lou, pressing tab, my lookup
would be here, then coma. And now instead of selecting
this particular row, I will select and I will put my cursor on the top of header. So you can see here
the table name and the column name appears
in different format. So now, our range has been converted into the table
name and the column name. So instead of giving
that particular range, we can give the table
name and the column name, and it is dynamic. There is no need to change
the range very time. So whenever we add
more data over here, Excel will automatically update this in the formula itself. Now I have to select
return array. So return array
would be I'm sorry, I have to change this
to course instead of date because date I have
to extract from this table. So this time I'm going to
select date and then Coma. And if not found, yes, I want to assign the value in text format that no courses. And this time I'm going to
select exact match because the course name must be
exactly matched with the values assigned in this
table in the course column. And then coma and this time, we have already selected
many times first to last, and this time, I'm going to
select search last to Fast. That's all. We have to close the bracket and
press Control inter, we got our answer.
The Excel basics. The last course was attended or done by trainer
is fifth of April. Let me change this
to another value. It's 21st, July 2025. One more course value
is coming over here, but system is fetching the
data from bottom to top. In the same manner, we can get the attendees
number using the Lou. It would be same, then it
would be same in this case, and written array
would be this one, then Coma, and nocurs
and then exact match, Coma, and last two first, closing the bracket Center. So Excel formulas,
21st of July and 36 is the number of attendees. This is how you can use different search options
in the X lookup. So in the next
video, we will learn more advanced options
related to the lookup. Thanks for watching. Stay
tuned to the Moe videos.
11. Multiple Column Lookup: Hello, and welcome once again in the Advanced Lookup
master class. In this video, I'm
going to explain one more trick related
to the lookup. So here I have some
data in two tables. One is related to the membership,
location, and prices. And here I have, sorry, I have person name and
type of membership he has took from the
agency and the location. I need to extract
a price over here, but this time, the
price is different. We have to check membership
and the location. Then only we will get the price. So basically, here,
two columns are involved for getting the
price from this table. So first of all,
we have to convert these two tables into the
official table format, which we already discussed
in the last video. So in the same manner,
we will select one cell, then click on Insert, select the table and then click Okay, I'm not going to change the
format, saving the time, selecting the insert after selecting the cell
of the second table, and click on table
and then Okay. And this time, I'm
going to change the table name to let's say the price data or something meaningful as
per the requirement. And here, price
structure or something. Okay. So now I have
to select this time two or the multiple
lookup array and the lookup value for finding the value
based on two columns. So let's get started. So equal sign. Look up. So lookup value, if we are
searching for one column, then it would be the membership. But this time, we
are going to join both membership and
the location to extrap the price based on the membership and the
location because here the memberships are
almost so it means we have four types of membership, but the location is different, and the price is depend on the location and the membership. So I'm going to join
membership with location in the lookup
value using the am posent. So selecting the location as well, and this
time, you can see, I have a membership and the location instead
of the cell name. You will see the magic
why I have converted into the official table later on once we complete
this formula. Now price the Coma
and this time, I have to select Lou array. Lou array also must be joined, I will select like this or
I can click on the top, and the cursor would be
changed into the down arrow. And I will type posent. I will select same way
this area as well. And now Coma, I will
select to price. It will come automatically. And now you can see we
did not get any range. Instead, we got the name of the table and a related column. And then Kuma not found, so zero and exact match I need, and we don't want
search more this time. I'm going to close this
and once I press Enter, we got the answer
in all my cells. So here I have not copy paste the formula to the last
cell, it came automatically. If I add more data here, there is no need to update
my function or the formula. It will be updated
automatically. So next time, you have to
use all your tables in the table format for this
advance use of any function. So this is the magic. So now let's cross check our result. So here we have one
person, one member, having the gold membership
and the location is London, and the result is 10y. So I'm coming to the gold
membership in the London side, so it is 10, and that
is perfectly fine. So this is how you can use the multiple columns
in the Excel. Thanks for watching Stay
tuned to the next video.
12. Two Way Lookup: Hello, and welcome. Once
again, in today's video, we are going to learn one more
advanced trick of lookup. I have two tables
in which I have accommodation type and the area and the particular prices. I need to extract the price based on the
accommodation and the location. In my previous example, I have combination of columns. But here, the problem is I have one data in
column and the second, the data which we need
to check in the row. So that is called two way Xocup. So how we can achieve
this, let me start. Using the lookup.
So first of all, I have to convert this table
into the official table. So I'll select one, Guru insert, click on Table, and this time, I'm not going to change the name which you can do
for your practice. Also, I want to
convert this into the official table quickly
for saving our time. Now I will use the lookup
to do the two way X lookup. So this time, I'm going
to select X lookup, restare, and I have to
select the X lookup. I mean, the lookup value
would be accommodation. Instead of Salar range, accommodation column value
is coming Coma lookup array. So I have to select this
lookup array and then coma. Now I have to again
start the next lookup. So it is called X up
inside the X lookup. So this time, I'm
going to select X look up once again and this time, the lookup value
would be location. And where is my location?
It is in the row. I will select the head
apart and then I will press Coma and then the return array would be this complete area. Okay, then coma, there is no need to define any
other optional value, so you can remove it
and you have to put two closing brackets and press
Inter to get your values. And for your convenience, I am using formula, text to get the. What is the function
I have written? So you can see I will move
to the downside over here. Sorry, it has changed. So I will change it again too. Give the reference over here. So now I'm going to explain once again this
complete structure. So I have one table in which I need to fetch
the price based on the accommodation which
is in the column and the location which
is in the header of this table means in the row. And the price would be the intersection of the area
and the accommodation time. Let us say check this one
tree house in the Yorkshire. So I have three house, Yorkshire and the
intersection is dollar 160, and it is coming
over here by using this complete X lookup. So what I did, I
assigned my X lookup, first X lookup value
is accommodation, and I have given my
a lookup array in my first table and selected
the accommodation column. Then again, I use
the Xu function to extract the data based
on the intersection, so I decided to give the location as a lookup
value from this table, and my lookup array
was the head apart, and I gave the complete range of the prices starting from
the C four to F seven. The benefit of using this
table structure already explained to you that whenever
I add more data over here, that will be updated
away in this function. So this is the benefit
of using the tables. Hopefully, it would
be very helpful to you for your practical
real world examples. So thanks for watching. Say
tune to the next video.
13. Three Way Lookup: Hello, everyone. In
the previous section, we have discussed about
a two way lookup. In this video, I'm
going to explain how we can do the three way
lookup in the Excel. So we will take one example. Let's say I have
some Dera over here, and I need to extract the rate
based on the three inputs. First is room type. Second is board. I mean, the type of boarding
the customer has taken in the weekdays. So there are three
arguments which we need to use to fetch the price
which is written over here. So the intersection of
this data is the price, which we need to fetch here. For this, we will use
the X Lu function. But here I will also explain some advance array functions provided by the Excel
in 2026 to you. So what we need is to create a drop down
list for room type, board and weekdays over here. So I'll change it
to day actually, and this will be the price
which we need to calculate. So these are the
things we need to do. So first, we will create
the dropdown list. For this, we need some helper
columns which we can hide. So here we will create
three helper columns. So first, I need unique
values of room type. For this, we will
use unique function. It's very simple, equal sine, unique, plus tab,
and give the range. If it is converted into
the official table type, it would be easy because
it becomes the dynamic. Means whenever you add
a new data over here, maybe the new room type, it will get updated in
this list automatically. But right now I'm not converting this staple into
the official type. I'm closing this one
and press Enter. So we have unique
data over here. And now I will come over here for creating the drop down list, come to the data type, come to the data validation, select data
validation from here. And from here, you can
select a list type. And in the source,
we have to give the reference of this cell. It is K four, K four, and after that, we have to put Hash. Otherwise, it will not
consider this complete list. It will consider only the
cell value K four only. So now click Okay, and we can see I have
a drop down list, so we can select executive gold and silver, whatever we want. In a similar way, we will
do it for boarding type. So I will type Unique and come over here,
closing the bracket. And from here, I will
go to Dera tape. Come to data validation,
data validation, then list, and here would
be equal four hash. Now, Okay. So here
I will get it. And now is the tricky part. We have data this
time in the row. So first, we need
to transpose it, and then we need to
use unique function. First, I will convert transpose. For this, we will
use first transpose. And in the array, I need to use Unique, and then I will give the
range of these all data, then closing the
bracket for unique and then closing the
bracket for transpose, presenter, and we got the data, all unique data in this column. From here, I will go to data. And then data validation. Again, select data validation, then the list and from
here four hash click. Okay. Now we can select the
dF this drop down list. Now it is time to use
X lookup three way, eqsine X lookup and then we have to combine
room type and the board. We have already done this, so we will combine
using the percent and the board because both
are in the columns only then coma and the Lou array is in columns
that also we have to combine. I will select this
range then percent. I will again select this
range and then coma. And now in the return array, I need to use again X lookup. X look up inside the X up. I will press tab, and this time, I'm
going to select C two, then coma and the Lou array would be here all
these days, then Coma. And in the return type, I need to select
this complete data. Then I will close
the bracket for first lookup and the second
lookup and press Enter. So this time we have
executive full Wednesday. Executive full for Wednesday, the price is 899. Now I will change
any particular day, so it is 806 and it is 806. Let's change the
room type to silver. So the price is 496. And this is silver
full on Sunday. So this is how you can use. You can also hide these columns, so no one knows from where
these values are coming. So this is the way you can use three way lookup in the excel using the
X lookup function.
14. Three Way Lookup with Range: Hello and welcome to the
X Loup master class. In the previous example, we have discussed
three way lookup. We will continue with
the same example. But here, we have a date range
instead of a fixed date. In the last example, we have a fixed day of the week like Monday, Tuesday,
and Saturday. But this time we have to fetch the value between
a particular date. We will use the argument for
fetching the information. So let's get started. First of all, I would enter a date which is in
between these two dates. So let's say I will
consider 15th of Fab 2025. 15th, five, 2025.
I will enter that, and now I need to fetch
the price from this table. Okay? So for this, we will use Lou function. So the lookup value, that would depend on the
room type and the board. So we will join
these two values. So that procedure is quite similar to the
previous example. I will also join
the lookup array. So room type, range, percent and board
type. Then the CVA. Now the return array.
So in the return array, we will again use X up. And the date would
be this one, comma, and we will give the date
range for lookup array here, and the return array
would be this. Everything would be
similar, and if not found, then we can enter zero, and we will use minus one
instead of exact match. So this is the major difference from the previous example. In the previous example,
we have used exact match, but now we are looking for a smaller value from the
date range we have given. So if the date range between two dates means from first January
to first March. So less than first March means up to last day
of the February, the rate would be
applicable of this column. This is the impact of this
minus one argument value. So we will select
this one press tab, and we are not going to
select any search mode, et cetera, so we
are going to close. We will press Control Inter or command Enter if
you're using the Mac, right now, I'm on
the Mac system. So you can see we have silver
full and 536 is coming. So if I change to another type like executive or the gold, so rate has been
changed automatically. So this is the major difference using the three way
lookup if we use exact match or a smaller value
minus one in the argument. So in the next example, we will learn one more advanced
trick using the Excel cup for your daily office work or practical real life examples.
15. Lookup Values from Range: Hello, and welcome once again in the new episode of
Lookup Masterclass. So today, I'm going to explain
one more trick of Excel. I have a sales
table over here of different items month
wise. This is the data. Now I need to fetch all
columns here using the item. I have already
created one dropdown, so I can select a stock item using this dropdown.
I have two cases. First case is I have same month in the same order
as the origin data, but I have a different
table where I need to fetch the data based on the
month written over here, but it is not the same
order as the origin one. We have April 1
instead of January. So how we can achieve, I will explain in this video. Let's get started by using
the X lookup function. So I will type X lookup. The lookup value would be same. Lookup array would be same
way and return array. So instead of giving
a single column, we have to give the
complete range of data, close the bracket Control plus
inter, we got our answer. So if you change item from this drop down
list, let's say, coffee. So it will return the data
from this list automatically. So this is how you can
achieve if you have same order as the
origin lookup values. Now moving to our next example. So in this case, I'm
going to use Luka and this time the lookup value
would be this one Coma. Lou array would be the heading. Then the Coma, this time, I'm going to use X
lookup over here. So I will select item from here, and this would be
the lookup array, and this complete range
would be the return array. Now close the bracket
for both lookups. Now press Enter, and
we got the answer. Let's do the cross check. So here I am selected mofins. I will change it to another
item. Let's say toast. So this is the toast, and it is apparel. So in the toast, we
go to the apparel is 733 and June 7 237. Same data I am receiving. So let's do some
change over here. So if I change because
it's completely dynamic, so the price or the data will be fetched automatically
by the Ax carp. So this is how you can
achieve this task. Thank you for watching. Stay
tuned to the next video.
16. Invoice Automation with XLOOKUP: We have already discussed many tips and techniques
related to the Excel, especially in the Excel
in this master class. Now we will implement
whatever we have learned and discussed
in practical example. For example, I have to select the category from the drop
down list available over here, which is dynamically connected
with the master sheet. It means whenever
I update the data, if I delete any row, it must be update
automatically here. So it is dynamically
connected and I have to select any category. All items fall under that particular category
will be filtered here. That also dependent on
this drop down list. And whenever I select anything,
price came automatically, if I enter the
quantity, let's say, 75, amount has been calculated. Also I get serial
number automatically. How I achieve this, I will explain in this
video, so stay tuned. Start from the scratch. Let
us say I have this data. So I will copy this
complete data. I will copy. I will insert a new worksheet. I will rename to master. Press Inter, and then I will paste this data over here
using the passpatial. I will select text
and then Okay. Now we have to assign the
table name, item master. Now we have to fetch
the unique name of the category from this table using the unique function, and we have to transport as
a header in this first row. For this, we will use
transpose function. Inside, we will use
unique function and the array will start from
here, from the second row, shift control down heroki and we will close
the bracket for unique and for transport, and plus Inter, we got unique
category name as a header. Now we have to fetch
the product name depending on the
category in each column, which must be dynamic
means whenever we add or delete any
data in the stable, it must get upddd automatically. For this, we will
use filter function. In the array, we have to
select the product name first, shift control down arochi. Then in the include, we have to select
complete column from the second row
for the category. We will use shift control
down arochi, equalsine, we have to select F
one where we have entered the transpose
function for selecting the unique
values of the categories. After that, we have
to close press Enter. It might take some time
to fetch the data. Now we get the
item list based on the category mentioned
in the header. I will copy and paste this
function for fetching the item name based on this category name
mentioned as a header. I will select the header,
I will make it bold. And then from here, I need
to make it freeze top row. Now we have done our half part. Now I will come over here
as an invoice sample. I will copy this complete
area, I'll copy. I'll select a particular cell, then come to the home tab, selecting the pay
spatial option, and IV select format. And then. Again, I
will click over here, come to pay spatial and IV
select column width. Okay. Now, again, I will come
to the invoice sample. I will select the header, coming to my invoice sheet number two. Come over here and Control
plus V to paste an error. I will increase the size. I will zoom a little bit
to see the complete table. I'll go to View tab. And remove the grid lines. Now I will start my
working over here. So first, I need category
drop down over here. For this, first of all, I will select a complete column. Then I will go to data, data validation,
data validation, I'll select list over
here and equal sign, I'll come my master data. I'll give the reference of
the cell where I have entered the function transport with Unique for fetching the unique
values of the category. After that, don't forget to enter Has shine, and
then click Okay. Now we can see the drop down, which is completely dynamic
linked with the master. Now my task is to fetch the item based on the
category selected over here. I will select this
complete column, then again, go to data
type, data validation, data validation, data
validation once again, I'll select list over here, and this time, I'm going
to select lookup function. Equal sign. I need to type complete formula
name, break it start, and this time it will depend on this C six cell the
first category. But when we select by default, it will become constant, but we need to remove this
dollar sign from here. Otherwise, you will get the stock items based on
this C six cell only. It will not dependent
on the next cell, so make sure you
have selected that. After that, for cap array, we come to master. We will select the header
in the master of seat, shift, control, write hierarchy
to select complete range. After that, Coma, we have to select the
second row only from where we have entered
our filter function for fetching the stock item name, depending on the category
unique name over here. So second row only,
and after that, you have to close,
and at the end, do not forget to mention
the has sign for making it dynamic.
After that, click Okay. And let me see whether it's
fetching the data or not. We can see the drop down
over here, just click on it. I'm getting some
values, but we need to check whether
it is okay or not. I will select another category. Let's say supplies because it has list number of products. I'll click over here and I
can see a different list. Very few items are coming. Let me select any
particular item. After that, if I
click over here, you can see so many
items are coming. Now moving to the next part, whenever I enter any quantity, price should come automatically. So whenever I select any item, price should come automatically, and when we enter any
quantity, amount should come. Also, I need the serial number. So let me start with
the serial number. So select this for this, I will use my function, count A with I function. I will start with I
I'll give the reference of this cell is not blank, then I need to count A from this cell and give
the reference of the first cell from where
we mentioned the category. Make it constant. Then again, we have to select,
but this time, I'm not going to
make it constant and then close the bracket, else, we have to enter. To double quotation mark, then press Inter to accept it. So I'm getting one. Now I need to fetch
to the last row. So now I can see I
have two over here. Now if I select
another category, three is coming automatically. Now my next task is
to fetch the price. This is not blank. Then we need to use
X Lou function. That is depend on this value. So lookup value
would be the item, and lookup array
would be the master. So here we have product name, I select shift down Aaroky, then Coma, I need
to fetch the price. I need to select this price
column from the second row, shift shift control
down aro key, then Coma double quotation mark closing the bracket
for I function. Now press Enter. I will fetch this formula
to the down as well. Now I'm getting the
price as well over here. So let me select another item, I got the price automatically. Now I need to calculate the amount whenever I
enter the quantity. So for this, I will use if
this one is not blank coma, I need to do multiplication
of quantity with price. Otherwise, it must be blank. Closing the bracket,
Control plus enter, dragging to this function
to the downside. Now I will enter the
quantity like 15, ten, 20, calculation has been done automatically for
doing the aggregation, for calculating the sum, I will use the aggregate function. I'll select aggregate. I want to do sum. Then I will select
the option from here, ignore nested subtotal and
the aggregate function. Coma, I'll give the reference of this column closing the
bracket Control plus Enter. I got the calculated
amount over here. Now let's feed
some data to check whether everything is
working fine or not. This time, I'm going
to select Alt down arrow key to select my
category from this drop down. I select category, press step, serial number came
automatically. I will select item from here. So all item fall under the binders category are
filtered automatically. I'll select any particular item. We can type the name
or we can select every durable binders,
then pressing Inter. And we can see price
first automatically, but the amount has
not yet calculated. After that, I will enter some
quantity, let's say, 149, press Inter cursor move to the category cell
in the next row, and I got the amount
in the amount column automatically and subtotal
also calculated automatically. This is how you can
do the automation in Excel using some advanced
tips and techniques. Hopefully, you like this video, stay tuned to the next video. Thank you.
17. XLOOKUP With SUMIFS: Hello, and welcome
to another episode of X Lookup Master Class. In this video, I will take example of calculating the
sum using the X lookup. So when we calculate
the sum of the values, we need numbers to calculate. So first, I will give you
a very simple example calculating the sales
value depending on a particular item from here. After that, we can use Sum Is function for multiple criteria. Here in the advanced one, I will select a particular
month and the item, and we will get the
total over here. For this, we will use SIs
function, but before that, I need to calculate
the total value of a particular month
given over here. So I want to make it dynamic
instead of static so that the user can change the month using
a drop down list, how to create
dropdown list we have already discussed and
created many times. So this time, I'm
not going to create. Also you have to create dropdown list for
your practice here. So let us select a
cell and type some, and then we will use for fetching the numbers
using lookup. The lookup Valet would
be here then Coma, and the months has been
given on the header part, so we will select this complete
header, and then Coma. In the return array, we have to select this sales data only. We will close the bracket for
lookup and the sum as well. Once you press the Enter,
we got the total of thousand 6,511 total of sales
for the month of apparel. So let me cross check. So
if I select these cells, so I can see some values
are coming over here, it is completely correct. Now I will change to March
and it get changed to 6,603. So that's how you can create a dynamic function for
calculating the sum, now moving to our
advanced function. That is sum Is. So as you know that SIs are used to calculate the sum
based on some criteria. If you're new to
the Sumix function, I'll show you how we use SumixFunction and what are
the arguments we have. So some ifs are
used to calculate the sum of a particular range based on the
different criterias. So if is used for
single criteria, but if have multiple criteria, so we will use
some ifs function. This time, I'm going to
use some ifs function. So first of all, we have
to define the sum range. So as I told you, we want to make it dynamic, means we have different
month given over here. We have sales data of
coffee in the area one, then Area two, and
this is area three. So I want to calculate the sum based
on a particular item. So I will select,
let's say in January. So this is my sum range
for calculating the sum. Then the next criteria range,
what is the criteria range? So here I have items, and then we have to
define a criteria. So this is my criteria. If I close this one presenter, so I get 2,155. That is the total sales
values of the mopins. So you can cross check, select area one, then area two, and then area three, and
here we can see 2,155. So this is the use
of Sis function. Now let's move to
make it dynamic for calculating based on the
month and item from here. Our function, which
is sum ifs function, not the sum if otherwise, the arguments are different
if you select semi function. So here I have to
define my sum range, which must be dynamic. Dynamic means we also
maintain our month over here so that column must be changed according
to our requirement. So for this, we will
use X lookup function. So first, I will select
the month, then coma, then the lookup array would
be the complete head apart, and then coma then
we have to select the complete area of the table. Then I will close
the lookup function, then coma now I need to
define the criteria range. Criteria range
would be the items. I will select this column and Coma and this would
be my criteria. And I will close the
bracket for some function. Then if I press Inter, now it is completely dynamic. So Mopins sale in the
month of March is 2,556. Now let me show you this is Mfanes and this is
the March month, and I will select
my second cell, and this is my third cell. And we can see
2,556. Is the total. Now if I change to
April and if I change any other item like
X, it's X, basically. So for X, so this is
apriln and this is X, and I will select area
two and area three, and the total is 2,436. So this is how you can use SIs function with X Luka
for calculating the sum. This is very useful
when you create dashboards because there we need to put some amount and
we need to calculate the sum based on that
particular area. Hopefully you like this video. Thanks for watching. Stay
tuned to the next example. Thank you.
Skillshare Member
