Building Computer Skills to Enable Success

(03) 9999 7780

Call for Queries?

Embrace the Data Dance with Microsoft Excel VLOOKUP Tutorial

A focused individual works on multiple computer screens filled with spreadsheets, likely engaging in an Excel VLOOKUP tutorial to master data manipulation.

Mastering Microsoft Excel VLOOKUP

Understanding VLOOKUP Basics

It’s time to explore the wonderful world of VLOOKUP! It’s like a search party for data inside of Microsoft Excel, helping you to find what you’re looking for without going through an endless sea of information. This premade function helps us search across columns like seasoned detectives (or a lost sock in the laundry). To unleash its power, we type =VLOOKUP in a cell, and voilà!

VLOOKUP allows us to pull data from the depths of our spreadsheets, and it requires some key components:

Identifying the VLOOKUP Syntax

The syntax for VLOOKUP looks like a bit of a tongue twister but don’t worry, we’re all professionals here! It goes like this:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Breaking it down further:

  • lookup_value: This is the value we want to find. For example, it could be a product ID like “P123.”
  • table_array: This range contains the data we want to search. Imagine it as a buffet of data waiting to be sampled!
  • colindexnum: This indicates which column’s value we want to return. For instance, if we want the price from the second column in our array, we set this to 2.
  • [range_lookup]: This indicates whether we want an exact match (FALSE) or an approximate match (TRUE), looking for friendly numbers or close ones.

Now, when we input this into Microsoft Excel, it searches for the specified lookup value in the first column of our defined table array and pulls the data from the column we desire. No more hunting through spreadsheets like it’s a treasure map! For more advanced features and practices, don’t forget to check out our guides on advanced excel formulas and excel spreadsheet tips.

Using VLOOKUP effectively can truly transform our spreadsheet experience from frustrating to fabulously efficient!

Enhancing VLOOKUP Functionality

In our quest to master Excel VLOOKUP, we find ourselves at the crossroads of micro-management and data-driven enlightenment. Here, we’ll explore how to utilise exact and approximate matches, along with strategies to overcome the limitations lurking in the VLOOKUP function.

Utilizing Exact and Approximate Matches

When wielding the power of VLOOKUP, we can decide if we want to find an exact match, or if we’re ok with approximation. For those moments when an exact match can’t be found in our designated table array, we can opt for an approximate match. This is a simple tweak: we just change the ‘0’ or ‘False’ in our VLOOKUP function to ‘1’ or ‘TRUE’. Voilà! Our trusty formula will now identify the closest match beneath our input value.

For instance, if we enter ‘Gala’, the nearest culinary cousin might just be ‘Apple’, which could return a delightful $0.23. Now that’s what we call a fruity discovery!

Lookup ValueClosest MatchReturn Value
GalaApple$0.23

This flexibility in matching greatly enhances our data retrieval gameplay. To read further about advanced Excel formulas, make sure to check out our article on advanced excel formulas.

Overcoming VLOOKUP Limitations

As much as we adore VLOOKUP, it has its limitations that could trip us up in a spreadsheet duel. VLOOKUP won’t automatically update when we make changes like adding a new column. Plot twist! To counter this drama, we can combine the magic of INDEX and MATCH functions to maintain robust data retrieval.

Another common VLOOKUP issue involves the requirement that the lookup value must reside in the first column of the specified range, with return values dwelling securely to the right of it. If we accidentally forget this cardinal rule, we may see frustrating errors like #N/A or even the dreaded #REF!

To sidestep these annoying pitfalls, we can follow some best practices. This might include using absolute references, steering clear of numbers or dates stored as text, sorting the first column, and properly handling wildcard characters. For more handy hints, visit our guide on excel spreadsheet tips.

Beware of mismatched data types as well, as they can haunt our VLOOKUP results! It’s suggested to employ Microsoft Excel’s built-in error correction to convert columns to their rightful formats or use the Text to Columns tool to restore order in our data (Microsoft).

Armed with these insights, we can now enhance our VLOOKUP functionality while sidestepping the potential traps!

Excel VLOOKUP Best Practices

When we dive into the world of the Microsoft Excel VLOOKUP function, there are some best practices we should embrace to avoid the gremlins that can wreak havoc on our data. Let’s avoid those common errors and improve our efficiency like pros!

Avoiding Common Errors

The dreaded #N/A error is the bane of VLOOKUP users everywhere! Some of the common errors we might encounter while using VLOOKUP include:

Error TypeDescription
#N/AThe lookup value is not found in the first column.
#REF!The column index is not valid.
#VALUE!The table array is not a valid range.
#NAME?Missing quotes around text or incorrect function name.
#SPILL!Implicit intersection of multiple values.

For more tips on managing these errors and making your VLOOKUP life easier, check out advanced excel formulas.

Beyond VLOOKUP

As we venture beyond the trusty VLOOKUP, we stumble upon our formidable friends: INDEX and MATCH, followed by the new kids on the block, XLOOKUP and XMATCH. Let’s a look at these functions.

Exploring INDEX and MATCH Functions

INDEX and MATCH functions go together like vegemite and toast. These two work so well together, even if individually they leave some people craving something more.

The INDEX function allows us to fetch the value at a specific position within a given range. Meanwhile, MATCH comes in to locate that specific position based on a certain value. When combined, we have a powerful alternative to VLOOKUP. Instead of being confined to fetching data only to the right of our search key (goodbye column limitations!), we can look left and right. Here’s a quick look:

FunctionPurpose
INDEXFetches a value from a list based on row and column numbers.
MATCHFinds the position of a value in a range.

To illustrate:

=INDEX(A1:B10, MATCH("Value", A1:A10, 0), 2)

In the example above, we’re fetching the value in column B that corresponds to “Value” found in column A. Simple but effective!

But wait, there’s more! If we want to tackle the infamous mismatch of data types affecting our results, we can also use Microsoft Excel’s built-in error correction tools. For example, if “Value” is a number but hidden as text, we can convert it before the MATCH function gets confused like a cat in a dog park. More on that can be found here.

Embracing XLOOKUP and XMATCH Functions

Enter XLOOKUP and XMATCH: the supercharged successors to our old friend VLOOKUP and its MATCH cousin. With these shiny new functions, life’s much easier, and we wonder how we ever managed without them!

XLOOKUP allows you to search a range or array for a specified value and return a corresponding value from another range or array. Unlike VLOOKUP, XLOOKUP can search both left and right! Talk about liberation! Here’s the syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XMATCH is just the MATCH function, but on steroids—allowing you to find the position of an item in a list or range, but with more options for exact matches and wildcard handling as well.

FunctionKey Features
XLOOKUPFlexibility to search left and right, custom not found message, flexible match options.
XMATCHImproved arguments for exact matches, wildcards, and reverse search order.

With these tools, we can easily correct our past mistakes and finally win at the game of data retrieval. For those feeling experimental, we can always try concatenating inputs within the XLOOKUP function to get creative with our searches.

For more on Microsoft Excel’s advanced features, we can also check out advanced excel formulas or see how to automate data entry with excel macros for beginners.

Now that we have dipped our toes into the waters beyond VLOOKUP, you might be feeling ready to take your Microsoft Excel skills to dazzling new heights!

Share This Post

Keystroke Learning - Building Computer Skills to Enable Success

Learn Today Face to Face Online with Keystroke Learning

Learn with Keystroke Learning

Our training approach is professional yet friendly, motivating participants to learn quickly and easily. 

When you complete our training course, you will be ready to apply your new skills right away. 

If you have a customised training solution in mind or need to schedule our popular computer courses
Interested in learning more about our offerings?
Our team will take the time to discuss your requirements and help you make smart decisions that best meet your needs.
© 2016 Onwards – Keystroke Learning – ABN 11 620 050 759
Keystroke Learning - Building Computer Skills to Enable Success

Log in

Keystroke Learning - Building Computer Skills to Enable Success in Melbourne

Melbourne Training Centre

Suite 613, Level 6,   1 Queens Road Melbourne, VIC 3004

Venue Access Hours

Monday – Friday 8:30am – 5:00pm

Are you Running Late???

Keystroke Learning - Learning Methods - Self Paced Assisted Learning

Self Paced Assisted Learning

Self Paced Assisted Learning combines the best of independent learning with the best of trainer assisted learning.

This unique approach to learning works as follows:

Each participant is provided with a training manual for a given application and level, and they work through a series of exercises.

If they get stuck or don’t understand a concept, the trainer is immediately available to help them understand the issue at hand and to give examples of how to implement the feature or application.

It is not uncommon to see one person working on an Excel Advanced manual while the person beside them is doing a Word Introduction, and another is working on something else.

Active Monitoring

The role of the trainer in the Self Paced environment is quite different to their role in a typical classroom. Even when each participant is working well unassisted, the trainer will be actively monitoring their progress, providing feedback and encouragement as well as fine-tuning the learning process. There is usually little time to “take it easy” as the trainer works to increase the efficiency and effectiveness of learning.

Personalised Learning

Self Paced Assisted Learning is very personalised, with the interactions generally being around individuals and small groups.

These sessions can be broken into 1.5-hour blocks of time, allowing up to 4 sessions in a day, thus allowing a larger number of people to attend for shorter time periods.

Key Benefits of Self Paced Assisted Learning

Want to know more about Self Paced Assisted Learning? Reach out to us!
Keystroke Learning - Learning Methods - Webinar Training Courses

Webinar Training

We pack more useful information into our Webinar Training Sessions than a squirrel stuffs acorns for winter.

Ditch the sink-or-swim approach to new technology. Our Webinar Sessions are your team’s virtual lifeline to tech proficiency.

These interactive online gatherings transform intimidating rollouts into engaging adventures. Our expert trainers don’t just introduce change – they help make your team embrace it. We explain the ‘why’ behind new systems, turning sceptics into champions with concise, high-impact briefings.

Forget dull lectures. Our sessions spark lively discussions and showcase features with contagious enthusiasm. Whether your team is office-based or remote, we bring the learning to them.

Investing in training isn’t just smart; it’s crucial. Our Webinar Sessions are your compass in the sea of technological change.

Upgrading systems? Migrating platforms? Introducing new tech? We’ve got you covered. 

Curious about our Webinar Training? We’re here to help!
Keystroke Learning - Learning Methods - Seminar Training

Seminar Training

Is your team lost in the tech wilderness, sending smoke signals for help?

Let’s face it, expecting your staff to “figure it out” on their own is a recipe for chaos, confusion, and probably a few broken keyboards. Relying on this approach is like using “password123” for your bank account, technically possible, but you’re just asking for trouble.

Enter Seminar Training, the GPS for your technologically challenged employees!

Perfect for large groups needing to get up to speed, our seminars address organisational changes, explaining the rationale and benefits. We encourage audience interaction, generating discussions that’ll make your team feel engaged.

These sessions provide concise overviews of new systems and features, delivering targeted briefings with minimal disruption. It’s like speed dating, but instead of potential partners, your employees meet productivity boosters!

Our expert trainers don’t just demonstrate key functionalities, they perform them with the flair of a Vegas magician. To maximize the learning impact, many clients complement our seminars with our Roving Training program.

Whether you call them seminars or awareness sessions, when you’re upgrading, migrating, or introducing new technology, we’re here to save the day and your team will be begging for the next software update.

Got a questions about Seminar Training? Contact us for answers!

Keystroke Learning - Learning Methods - Roving Training

Roving Training

Roving Training brings expert facilitators directly to your workspace, either in-person or virtually, to address specific training needs.

Looking for training that’s as unique as your team? You might stumble upon a few buzzwords like “Roving Training”, “On-Demand Training”, “Floor Walking”, “Corporate Trubleshooting”, “Floater”, or “Roaming Training Services”.

Basically, they’re all just fancy ways of saying, “On-the-spot instruction tailored to specific needs”.

Roving Training is a 1 on 1 or very small group training session which is generally scheduled for short timeframe on a specific subject. 

Typically, these training sessions are structured by allocating time slots/appointments with individuals. The topics covered throughout the session do not have to be at the same level or software package.

Roving Training offers the benefit of immediate, hands-on support right where and when it’s needed, ensuring that learning is directly applicable and highly relevant to real-time challenges.

How Roving Training Works

In this example, Sarah, the office superhero has scheduled blocks of time for her colleagues. For instance, James is scheduled at 9:30am for half an hour to work on Pivot Tables. After James gets his Excel fix, the trainer visits Ellen at 10:00am for an hour to help solve a problem she is having with Photoshop. The trainer then continues visiting each person in the schedule, addressing their individual requirements.

Sarah also handles training during software upgrades and rollouts with one of our nifty tactics called floor walking. This involves the trainer roaming around the office to provide instant, on-the-spot support for any tech hiccups.

It’s like having a tech wizard who appears out of nowhere when you need them the most, no magic wand required, just a trainer with lots of expertise and a friendly smile!

Key Benefits of Roving Training

Want to learn more about Roving Training? Talk to us today!
Group Training Courses - Keystroke Learning

Group Training Courses

Upgrade your colleagues from “Control+Alt+Defeat” to “Tech+Savvy+Elite”!

Attention all tech-challenged teams and software-phobic departments! Is your office filled with cold sweats at the mere mention of pivot tables? Does the phrase “software update” send shivers down your spine like a horror movie jump scare? Fear not, our Group Training Courses are here to transform your tech terrors into laugh-out-loud learning adventures!

Our Full-Day Courses are the brain boot camp you never knew you needed. No drill sergeants or questionable cafeteria food here, just a day packed with digital discoveries. 

For those who prefer their learning strong like their coffee, our Half-Day Courses deliver an espresso shot of education in just 3 hours.

And for the ultimate in tailored training, our Customised Courses are the Tinder of tech education. Swipe right on the modules you love, left on those you don’t. It’s all about finding your perfect match in the digital dating pool.

So, let’s turn those frustrating error messages into exhilarating “Eureka!” moments.

Full Day Sessions

Full-Day Group Training Sessions provide a deep dive into your chosen subject, offering a comprehensive experience that’s perfect for teams eager to explore topics in detail.

Think of it as an intellectual day spa, with plenty of time to soak in each concept, from the fundamentals to the fine points. Your brain cells will thank you for the pampering!

With a full day dedicated to learning, participants can engage in extensive hands-on activities, workshops, and real-world applications.

You’ll have ample opportunity to tackle complex problems, ask questions, and even share a laugh or two as you work through exercises.

A standard training course takes around 6 hours, plus scheduled breaks to ensure everyone stays refreshed and ready to absorb more. We promise not to make you do jumping jacks during these breaks… unless you really want to.

The start time of the training is completely negotiable, but typically, the training time slot will be 9:00am – 4:00pm.

This turns the training day into an enjoyable and productive fun run rather than a sprint. Don’t worry, the only medals awarded will be for “Most Enthusiastic Learner”

Half Day Sessions

Half-Day Group Training Sessions offer a focused exploration of your chosen subject, providing a compact yet impactful experience that’s ideal for teams looking to dive into topics efficiently.

Think of it as an intellectual boost, with a concentrated dose of knowledge that’ll leave your brain feeling refreshed and invigorated. It’s like a shot of espresso for your mind, minus the jitters!

In this condensed learning sprint, participants engage in targeted hands-on activities, workshops, and real-world applications. It’s a mental obstacle course designed to challenge and stimulate without being overwhelming.

You’ll have the chance to tackle key problems, ask burning questions, and maybe even crack a few witty one-liners as you zip through exercises. After all, who said learning can’t be snappy and snazzy?

A standard training course takes around 3 hours to complete, which includes a short break at the halfway point. Don’t worry, we won’t make you solve riddles to earn your break—unless that’s part of the training, of course!

The start time of the training is completely negotiable but typically, the training time slot will either be 9:00 am – 12:00 pm or 1:00 pm – 4:00 pm. Whether you’re an early bird or a post-lunch learner, we’ve got you covered.

Off the Shelf Courses

Off-the-Shelf Training is like walking into a bookstore and picking out the perfect novel, it’s ready to go and has been vetted for quality.

These pre-designed courses are packed with tried-and-tested content, so you can dive right into learning without any fuss.

Whether you’re tackling Microsoft Office, Adobe Creative Cloud, or any other major software, these courses are designed to cover the essentials with a dash of flair.

Just as how you wouldn’t need to re-write a bestselling novel, you don’t need to re-invent the wheel with these training options.

They’re perfect for when you want solid, no-nonsense learning that’s ready when you are.

Customised Training

Customised Training is the tailor-made suit of the training world.

Think of it as the Gucci suit of the learning world — bespoke, fitted, and perfectly aligned with what you need.

It’s all about designing a training experience that’s as unique as your organisation.

Just like a custom suit is made to fit every curve and seam, customised training is crafted to address specific challenges, industry nuances, and skill gaps within your team.

Our training is modular, so you can mix and match like a fashionista, choosing from a selection of existing topics, or we can work with you to develop something specific to your organisation. Either way, we are on hand for advice on the best way to structure your customised training course.

Do you need a custom training solution? Get in touch with us!

Keystroke Learning - Learning Methods - 1 on 1 Training Session

One on One Training

Designed for participants who already have a basic understanding of an application but need to fill specific knowledge gaps.

The 1 on 1 training option provides a completely personalised training experience.

Brief and Focused

These sessions are entirely focused on the needs of the individual participant, whether that need is to become an expert in Microsoft OfficeAdobe Creative Cloud, or simply to become more proficient with other commonly used software applications.

1 on 1 training sessions are delivered in 3-hour blocks, while additional 1-hour blocks can be added if required. The choice is yours.

Help with Multiple Applications

Although the primary focus may be on learning a specific application or feature, this unique training provides participants with the opportunity to receive assistance on other applications during their session. This may include ExcelWordPhotoshopStoryline, etc.

Key Benefits of 1 on 1 Training

Interested in 1 on 1 Training? Let’s discuss your needs!
Keystroke Learning - Public Scheduled Training Courses

Public Scheduled Training Courses

Raise Your Skills with Expert-Led
Public Training Courses!

Are you tired of fumbling through Excel like it’s a complex puzzle? Ever wished you could wave a magic wand to fix your an image?

Well, Keystroke Learning’s Public Scheduled Training Courses are designed to meet specific training needs.

People from different companies all attend the same course at the same time, turning our Melbourne CBD training room into a bustling hive of learning and collaboration. Everyone brings something different to the table!

Can’t make it to Melbourne? No worries! We offer our courses online and in hybrid mode too, so you can join from anywhere.

Check out our Public Course Timetable and take your skills to the next stage!

Want to know more about our public training courses? We’re here to help!
Skip to content