3 things we’re covering today:

• VLOOKUP's overlooked power
• Let's meet in person at the Global Excel Summit
• Fun emoji blender

🔍 The Overlooked Benefits of VLOOKUP​

Here's a common Excel problem you'll run into at some point:

You have a range of numeric values and you need to categorize them based on specific boundaries or "buckets". This example uses grades but you can use the same solution for anything that has a similar setup, from salaries, revenue, performance metrics or credit scores - this scenario is a frequent one in the corporate world.

How would you go about it now? Manually?

Nah, that would take ages. You're in Excel, you're going to use a formula.

The first function that might come to mind is the IF function. It works, but you'll have to account for several conditions - this means multiple nesting levels.

It would go something like this:

"IF the Score is lower than or equal to 55, then the Grade is F; if not, then we have to check the next condition. If it's lower than or equal to 69, then the grade is D... and so on and so on"

...you see where this is going right? Stepping away from the screen 😅

Let's say you decide to go through with it and also use cell references for the conditions. You'll add a new column beside the buckets and type the upper bound. Then you power through your IF function.

=IF(C2<=$G$2,$H$2,IF(C2<=$G$3,$H$3,
IF(C2<=$G$4,$H$4,IF(C2<=$G$5,$H$5,$H$6))))

It works, but it's long and complicated. Imagine how you'll feel if you need to add new buckets in the middle 😓

There is a much simpler solution:

Just use the good old VLOOKUP.

Just like before, you'll need the lower bound of the score ranges. Here's a cool tip. You don't need to type these out manually. The quickest way is to use Flash Fill. Just populate the first number, then press the shortcut key Ctrl + E (or use the fill handle and select Flash Fill). All the lower bounds will be populated automatically.

You can now start writing the VLOOKUP formula.

=VLOOKUP(C2,$G$2:$H$6,2)

Yep. That's all you need to write 😎

Then simply copy the formula down. All the grades are assigned in a matter of seconds.

Important: Make sure the numbers in your bucket list go from small to big. If they don't, VLOOKUP won't work right. Good news is, with XLOOKUP you don't have to worry about this!

=XLOOKUP(C2,$G$2:$G$6,$H$2:$H$6,,-1)

where -1 stands for "exact match or next smaller item".

So, at least in this scenario, the trusty VLOOKUP is the winner when it comes to formula efficiency.

Try it next time you have a similar situation. I recorded a shorts for it.

🕵️ Challenge time: There's an unintentional oversight in the data setup. Can you spot it?

If you do, you can call yourself the "Excel Sherlock" of the day! (scroll to the bottom to find the answer)

​

🌍 Global Excel Summit 2024: Excel Unleashed!

Get ready for an epic Excel journey! The Global Excel Summit is back and better than ever, and I'm excited to announce that I'm your ambassador for the third time!

📅 Save the Date: February 6-8, 2024

📍 Location: Vue Cinemas, Angel Central, London, UK

Here's the Scoop:

🤝 In-Person & Online: Join us in London (6-7 Feb) or virtually (6-8 Feb).

🎤 30+ Speakers: Dive deep into AI Exceleration sessions.

🎓 CPD Credits: Awarded accreditation by the CPD Standards Office in 2022 and 2023.

💰 Exclusive Discount: Use code LEILA for 20% off Early Bird prices (until Nov 27).

Ready to Excel? Don't wait too long - in-person seats are limited. Secure your tickets on the official website.

Let's geek out over Excel & AI, connect with pros, and make memories!
​

🤓 Geeky News

📊 Excel - Control Automatic Data Conversions

I made a video a while ago showing how you can use this feature to allow leading zeros when writing IDs or product codes.

It's finally moving into general availability, and will hopefully continue to roll out to more and more Microsoft 365 users. You can check if you have it by going to File > Options > Data > Automatic Data Conversion.

🤖 OpenAI ChatGPT - Major New Improvements​

OpenAI is rolling out new voice and image capabilities for ChatGPT to Plus and Enterprise users.

This means you'll be able to speak to ChatGPT and hear its reply in one of five voice options (on Android and iOS mobile devices). Future applications can include voice translation of podcasts.

You'll also be able to upload and discuss images, including photos and screenshots. It can be used to troubleshoot technical problems or analyze complex data, among other applications.

ChatGPT is also adding a 'Browse with Bing' feature which allows it to access real-time web search (allowing you to get the most current info as well as double-check its sources and ensure that it's not another case of AI hallucinations 😉).

Cool updates! I published a video last week on ways you can optimize your prompts for better outcomes - if you missed it, you can watch it here.

​

💚 My Favorite Things

🧑‍🍳 Have you tried Emoji Kitchen yet? It's been around for a while (in the Gboard app for Android), but now you can create your own emoji mashups on any device by typing "Emoji Kitchen" into Google Search in any browser and then clicking on Get Cooking.

You can achieve the exact expression you want to evoke or get crazy creative with your combinations.

You can also leave it to fate by using the randomizer.

If you want to share the end result, simply click on it to copy and paste it as a sticker.

Have fun with it!

​

🚀 Power Stories

Congratulations, Melody, on completing Excel Essentials for the Real World! Thank you for sharing your story on LinkedIn.

I'm sure a strong foundation in Excel will prove beneficial in your new job (congrats on that as well 🙂).

I applaud your instinct to follow it up with Power Query. It is THE tool for anyone who works with data and mastering it will make your working life a breeze.

Happy learning and all the best in your career!

Got a success story about using your XelPlus skills? I’d love to hear it! Hit reply to share the details, and inspire other students 😇

See you next week,

Leila️

🕵️Solution to the oversight in data setup: Our first bucket ranges from 0-55, and our second bucket is 55-69. The correct range for the second bucket should actually be 56-69.

​

Want more?

▶️ Subscribe on YouTube​

🖇️ Follow me on LinkedIn​

🥇 Join 400,000+ students in our courses​

📣 Want to sponsor XelPlus Weekly? Get in touch here.

This newsletter contains affiliate links, which give us a small commission on any purchase made at no cost to you. This helps us run XelPlus Weekly and bring you updates like this. Thank you for your support!