Excel Advanced Functions - Lookups

Excel Advanced Functions

lOOKUP functions

Please use the left and right arrows on the edge of each page to move through the presentation

CONTENTS

VLOOKUP

XLOOKUP Video

XLOOKUP Info

GET IN TOUCH

VLOOKUP

Lookup Functions

Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID. Tip: Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.

Click on the image to access the VLOOKUP Function video

CONTENTS

CONTENTS

XLOOKUP

Excel's new lookup function

For 35 years, VLOOKUP has been the first lookup function learned by Excel users and is the 3rd most used function (after SUM and AVERAGE). While VLOOKUP was widely used, it has several well-known limitations which XLOOKUP overcomes:

• Defaults to an “approximate” match:
• Does not support column insertions/deletions
• Cannot look to the left
• Cannot search from the back
• Cannot search for next larger item
• References more cells than necessary

XLOOKUP is named for its ability to look both vertically and horizontally. In its simplest form, XLOOKUP needs just 3 arguments to perform the most common exact lookup (one fewer than VLOOKUP). What’s the future for VLOOKUP & HLOOKUP? VLOOKUP and HLOOKUP will both continue to be supported by Excel. That said, Microsoft strongly recommend using XLOOKUP in favour of VLOOKUP and HLOOKUP because XLOOKUP is simpler to use and has none of the limitations listed above.

From "Announcing XLOOKUP" (Joe McDaid)

XLOOKUP

Lookup Functions

Use the XLOOKUP function to find things in a table or range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.

Click on the image to access the XLOOKUP Function video

CONTENTS

THANKS FOR WATCHING

WWW.BLUENOVATION.CO.UK

PLEASE GET IN TOUCH

CONTENTS