Transcripts
1. Introduction: While Microsoft Excel has a
lot of important formulas, functionalities that
you're able to utilize, yet many professionals, they
have no idea what are they? What are they used
for, and they rely on basic applications
because that's as far as they know in terms of
how to use Excel because there's a lot of information
that you learn from Excel. There's a steep learning curve that you need to go through to be able to utilize Excel
to its full capability. Well, we can just simply bypass that learning curve,
simply save us some time, save us the whole headache of
trying to learn Excel from scratch through
artificial intelligence, specifically Claude. In this current
class, I'm going to show you how you're
going to be using Claude to help you set up a very powerful
function and formula, which is often misunderstood, hence not utilized
as part of Excel, which is the X L. If you're using your data, you're trying to filter the
data, to understand it more, to drive some key insights
for your business, for your career, for your day to day activities
and operations. It's a very powerful
Excel formulation and functionality that you
need to be familiar with. And I'm going to show you how to use Claude to help you set this up in the easiest and the most simple way
possible to save you time, save your effort, and
get the best out of Excel in the fastest
way possible.
2. Your Project: Your project for the class
revolves around applying the key lessons taught to help you set up your Excel lookup. If you got your own
unique dataset, feel free to upload it
to Cloud and simply type in load data or simply navigate to the projects details and
simply download it as part of the provided
resources to help you apply a certain dataset
for the application. You could download the
dataset that I'm using, upload it to Cloud,
type in load, or just simply drag
and drop it to Cloud it's up to you.
That way you have the option of to get
the results as is, or to get the results uniquely tailored to your own
unique circumstances. After which you're
going to be creating the lookup through the lessons taught in the current class, and then you're going
to be sharing it with the rest of the
community for feedback.
3. Building Your Excel XLOOKUP: Welcome back. Now we are
ready to start integrating a very powerful component
of any actual application, which is the X lookup, which is some sort of an
advanced representation for the V lookup, where the V lookup
revolves around having the far most left
column as the reference for everything that comes along to the right of it, right? However, the X lookup is more fine tuned
towards identifying certain pieces of data based on not just simply a
single point of reference. Let me show you how
we could build this into any XL file through a clot. I'm going to mention we will create an interactive
X lookup in the chat before shifting the results to the
workbook on a new sheet. If you take a look at the
bottom right over here, once we say new sheet, once we are engaging
with Claude and you say New sheets going to open another sheet and another
sheet and another sheet, the same style
you're following as part of Excel for
you to understand basically how the
interaction happens in a way which is aligned with
the actual use of Excel. So if you're using
Excel, you got a workbook, which is
the one in front of us. You got different
sheets that you open. You got the formula bar on top. You got rows, you got columns. So that way, everything feels quite familiar to you
when you are dealing with Excel and mapping this
interaction through Claude. Now, once we are mentioning
the word interactive, which is one of the
most important words when you are crafting
the prompt to get the actual display on
the interface with Claude before mapping it
directly towards the results. For example, if you mentioned
create an X lookup, Claude will ask you
straight ahead, what are the columns
that you need to use? What are you trying to achieve? And then by default, will enhance the output directly
on the actual workbook, which could be problematic because when you are
dealing with a cloud, you need to download the file
and then open on Excel to finalize all of the components and to confirm that the
formulas are working. They do work perfectly 100%, but you're not able to see them directly live on the
actual interface. So the tactics I'm
teaching you right now, these are pro tactics to
help you get the results, confirm them and
just simply finalize the process in one go.
Take a look at this. We got the following X
lookup configuration, which is broken down
in a way based on the actual structure and the
functionality of X lookup. As you can see, we got the
function X lookup over here, which typically goes
at the top ribbon, the function bar where you
type the function manually. And typically when
you're dealing with complex functions like X lookup, V lookup, some if, and you try to layer all of
these different formulas, this is where the potential
for syntax error increases. As you can see over here, we've
got a comparison as well. X lookup advanced option
versus the typical V lookup, because X lookup is
considered some sort of an advanced version of the
application of the V lookup. Let's take a look at
the feedback over here. We got, for example,
lookup array. This is where we are going to be searching for the
specific order, you find the value
for that order. For example, let's say, I'm
looking in the column one, which is the one over here
for the order ID, right? And then find me
that specific value. Let's say I'm looking
for order 1003. What am I trying to find with respect to that
specific order? If you compare
this, for example, to a V lookup, you would realize
that it's more flexible. I have the option to, first
of all, identify the column. Let's say I'm switching it to
a certain sales rep, right? Then based on that column, which is the sales
rep over here, any specific data point, let's say I have the
following sales rep, James. Now, based on James details, what am I trying
to identify from the rest of the
columns, the revenue, the unit price, the customer names, the
customer segments, the region, whatever
detail that you have as part of your own
unique Excel file, right? Now, this is very powerful
because this helps you take your data analysis
skills to new level. If you notice as well, we got the lookup advanced
option versus DV lookup. If you have a certain
item which is not found, you can simply change
this to blank. And if you input the data, it will keep it as blank. Or if you have a certain error, for example, it will
reflect as not applicable, or you can put in a
custom text, for example, to help you better fine tune the results or showcase
a certain data. Let's say custom not found text, if I'm looking some sort
of for some details for a certain column I'm
trying to look out for, as you can see
over here, we have the color code for that
specific search value, right? However, I can simply shift
this to a custom text, I can simply mention not found. And whenever I'm searching
and there are no details that I'm trying to get or there are no details available
in my own chart, you will realize
that you'll have this custom not found text. Now, this is something like advanced representation
from Claude to help us understand
how we are able to integrate the X lookup feature, which is very, very powerful. Here's your interactive X
lookup fully loaded with all features that make it more
powerful than a V lookup. The core three inputs, for example, as a V lookup, you'll have the lookup array, lookup value, and
the return array all driven by drop downs. The three X lookup
exclusive options, this is where you have
the distinguishing factor between the V lookup
and the X lookup. If not found, what do you do? You choose a certain display
or a custom message. Match mode, exact match,
next smaller value, next larger value or
wildcard, search mode, first to last default
or last to first, useful when duplicates exist. You have the X
lookup feature that give you another
layer of filtration. That's the goal of the X lookup. It's a bit more advanced
than the V lookup. If you are having some a range
of datasets, for example, either you can mention exact
or next smaller number or next larger number. For example, if
you click on this, you can notice how it
impacts the output, right? Exact or next larger, let's say, exact or next smaller
or the exact match. Once you click on
the exact match, you're going to see the mode being reflected in
the actual results. Let me show you a
sample for this. Let's say I'm looking
for the unit price, which is the specific value, $89.99 and find it in
the unit price column. If not found, I'm
going to mention, let's say, not found in results. Match mode. Do I have Do I want the exact
match, for example, or do I want the exact
or next smaller, exact or next larger, right? So exact or next
larger, for example. So it will find me this value. If this value does not exist, it will find me the
next larger value right after it, right? So it's more of a
filtration feature, and we're able to see the
results when we are trying to and narrow down or
find out the output. Also, the search mode over
here, if I click on it, first to last or last to first is going to
toggle the data, as you can see over here. So we start from, let's say, from here, first to last. Notice what happens. We start from the actual lookup value. We go all the way
towards the end, the last value, or we switch
the option last to first. So we have the last value to be displayed over here, right? Then you go all the way to
the top, which is the first. So this is basically
a search mode feature because it highlights
the output. First to last, you
find the over here. Last to first, it highlights
the one over here. As you can see, based on
the filtration criteria, we are updating the
X lookup function. So this is more of
an advanced feature that many advanced Excel
users they tend to use to help them further
analyze or segment their data in addition to what's considered to
be the default option, which is the V lookup. So if you think
about the lookup, it's basically V lookup with additional features related
to searchability, right? Either you search
a certain option, either you match
a certain option, or if you don't have a result, what kind of an error message
you would like to report?
4. Integrating Your Excel XLOOKUP: Welcome back. So
let's say we are happy with the X lookup setup. Simply, I'm going to confirm
that migrate or move. It's up to you the
way you structure it. Now Cloud understands
exactly your goal. Migrate the X lookup
to a separate Sheet. Simple as that, Claude
will simply analyze the input prompt and then is going to add a new
sheet over here, and this depends on
your dataset that you are loading or the dataset
that you are using. All of these tactics apply to your own unique dataset as you are seeing the data
that you are engaging with. Simply to help you understand
the actual workflow and how you could actually map it to your own
unique application. So we're expecting to have
a new sheet over here, which reflects the X lookup. And keep in mind, all of
these details are functional, but you need to download the file once you are
done and open it on Excel in order to see the full functionality take place where you
change the values, you're able to see the
automatic formulas, automatic updates,
the lookup features, if you have any
additional features being integrated, for example, you're able to see them get updated on the spot
based on any changes. But as you're building your
dataset, your Excel file, you need to make sure
that you're following the lectures that basically
you're learning right now, and I'm teaching you to help you save time in
the entire process. And as you get the
hang of integrating Cloud as part of your
Excel activities, you can speed up
the entire workflow from hours to a
couple of minutes. Take a so we got 40 formulas
as part of the X lookup, how to use L. You got a drop down menu for
all columns, order IDs. We got a certain
match mode over here. These are exclusive to X lookup, exact, next smaller,
next larger, Wildcard, search mode, first
to last or last to first, custom if not message
no message was found. Also the 12 rows, for example, 12 populates all the
13 fields the moment a match is found with the selected return column
highlighted in green. As you can see, it's
explaining to you how to use the actual file
once you are done. You can simply download
this and run it on Excel. Let's take a look at the
final result. Here we go. This is the XX lookup. Which is very professional. And you can see we got
a small tip over here advantages bar or row,
X lookup advantages. Search any column,
not just the first, which is typically V
lookup application, return any array left
or right, built in, not found, matching, filtering, arranging, searching
in any direction. This is considered next
level XL application. And we can see the
steps over here. We got the Lou array over here, we can select which
column that would like. The lookup value, which is in step number two,
the one over here, then the return array from which field or column we're trying to
allocate the details, which is in the return
array over here. So if you click on
this and navigate to the actual current display, it's set at the ninth column. And once we download the file, we're able to take a
look at the results. And then if you take a
look at the match mode, this is the advanced
X lookup feature which you match the results
based on your search and you are able to arrange this is
a very powerful integration that even advanced Excel users have no idea how to integrate. Whether you are a beginner or someone who's well
versed with Excel, now you're able to apply
all of these tactics. In this current case, it's
lookup to help you navigate, identify and take
your data analysis, understanding, and
the buildup of your Excel file to
a whole new level.
5. Wrapping up : So what do you think?
Impressive, right? You're able to create Lookup, which is a very complex function to create through
Excel by itself, and you're able to utilize it easily through the
integration of Claude. I truly hope that you found the class helpful
if it helped you level up your
knowledge in terms of Excel and artificial
intelligence, especially Claude, then
it's a job well done. I look forward to receiving your feedback on the
current class and make sure that you follow
my profile for the latest releases and updates, and I'll see you
in the next class.
Engr. Hussein Attié, Entrepreneur I Engineer I Educator
