XLOOKUP and other Excel tech tips to enhance your Business

By Peter Lambert | September 27, 2019

1909_xlookup_i1063825508There are typically three "Wow" moments when learning to use Microsoft Excel...

1. Learning to do a SUM formula and dragging it across your data using AutoFill
2. Creating your first graph with a simple selection of your preferred data and clicking a button
3. Learning how to use the VLOOKUP formula to query a range of data for a specific result

Many users may never make it as far as #3, but for those that do, the VLOOKUP function is life-changing.

VLOOKUP, HLOOKUP, INDEX and MATCH...

Put simply, VLOOKUP will look for a value (either numbers or text) in a column. Once it finds a match in another column or datasheet, the VLOOKUP will return the desired value from any cell in the same row as the matched value. 

When the data is more intricate, VLOOKUP's cousins INDEX and MATCH are required, adding further complexity. 

While VLOOKUP searches for the value in a column, HLOOKUP (Horizontal Lookup) searches for the value in a row.

Now it gets even better with XLOOKUP

Microsoft have recently announced the upcoming release of XLOOKUP, a new function that rolls up the skills of VLOOKUP, HLOOKUP, INDEX and MATCH into a single, more powerful and more accessible function.

This new function basically allows you to search in any direction.

Unlike VLOOKUP, XLOOKUP is able to return an array with multiple items, which allows a single formula to return more than one item. For examples on how you can use XLOOKUP check out the Microsoft Office support site

This function is now available via Microsoft's public beta test stream, the "Office 365 Insiders Program".
Following further testing and quality assurance through the Insiders program the feature will eventually roll out to mainstream users of Office 365's Excel.

Bonus Tips on getting more from Excel

To keep you loving Excel until we all get to enjoy XLOOKUP, here are three tips on getting the most from your Excel spreadsheets.

1. Use"traffic light" conditional formatting for simple visual representation of your data.

Choose the Conditional Formatting drop-down menu from the Styles section of the Home ribbon on Excel, and from the Colour Scales sub menu choose the top left option, which should be the Green - Yellow - Red Colour Scale. Excel will automatically format your text in shades from Green to Red in relation to each other.

2. Autofill formatting, not just data or formulas.

Using Autofill to have Excel replicate pages of formulas has been THE key feature of Excel for some time, but did you know you can Autofill formatting without filling in formulas or texts? Grab the Autofill box with your right mouse button instead of your usual left mouse button, and drag down the series you want to fill. When you let go of the button, choose Fill Formatting.

3. Keep your data separated into sheets, don't set up multiple tables on a single sheet.

Having your data laid out in separate sheets with column labels at the top of each column with each row representing a record allows your spreadsheet to act as a database without fussing around re-formatting and exporting/importing data to get it prepared for use by an external applications. No formatting of spacing either, just keep it as data with simple (but elegant) formatting.

Need help with Microsoft Office 365?

Our experienced Business Technology Managers (BTMs) are available to assist you roll out an Office 365 or Excel project, or to ensure that you have an up-to-date and connected productivity suite. If you need help with yours, give us a call on 1300 307 907 or contact us via the form below.

 

Contact us today

 

TAGS: Tech Trends and Tips, Business Value, News and General, Software Development,

About Peter Lambert
Peter Lambert

Marketing specialist and technical blogger @ Diamond IT - I have over 25 years of experience in Information & Communications systems. My range of skills is diverse and includes extensive experience in desktop solutions, server and network presales and administration, VOIP phone systems, journalism, creative writing, technical writing, digital videography and audio visual streaming. I hold a Certificate IV in Training and Assessment, and I am an experienced classroom trainer and course coordinator. I hold an Advanced Diploma in Network Security, a Diploma in Network Administration, and a Certificate IV in Networking. I am a Cisco Certified Network Associate (CCNA) and Microsoft Certified Solutions Associate (MCSA).