Posts

XLOOKUP V's VLOOKUP - 8 Limitations Removed

avatar of @theexcelclub
25
The Excel Club
@theexcelclub
·
0 views
·
3 min read


XLOOKUP V's VLOOKUP

XLOOKUP, soon to be available in Excel 365 is going to make you rethink how you carry out your lookups. VLOOKUP comes with many limitations. Some of these limitations we can work around with other functions or combination of functions. But that is a pain…and time-consuming. Who needs all that hassle when XLOOK will remove these 8 limitations of VLOOKUP.

XLOOKUP V's VLOOKUP - 8 Limitation Removed

  1. You cannot carry out an exact lookup by default with VLOOKUP – Guess what? you can with XLOOKUP. Exact lookup is the default setting in the new function.
  2. You can not look up to the left with VLOOKUP – You can lookup to the left with XLOOKUP. As long as the arrays are of the same size (lookup array and return array), you can really lookup where you want.
  3. You can't use VLOOKUP for a horizontal Lookup – You can carry out a horizontal Lookup with XLOOKUP.
  4. Adding new columns to a table used in a VLOOKUP can break your formula. – You can add as many new columns with XLOOKUP as you like. There is no table array or column index to define in the next XLOOKUP.
  5. It is not possible to search from bottom to top of a column with VLOOKUP – With XLOOKUP you can search from bottom to top.
  6. Binary searches using VLOOKUP are not possible – You can with XLOOKUP.
  7. You cannot carry out an approximate match if the table unless the table is sorted smallest to largest. You can carry out a XLOOKUP without sorting the table. This is an amazing step forward.
  8. It is not possible to return an approximate match value that is higher using VLOOKUP. You can select to return an approximate match of a value higher with XLOOKUP. You can also select an approximate match of a lower value by default.

XLOOKUP is game-changer

XLOOKUP promises to be a game-changer when it comes to looking up values in Excel and returning a corresponding value. There are some many new features and function yet to be released in Excel 365 that will really blow your mind. XLOOKUP is for sure ranked high in the awesomeness of new releases.

What’s Next?

Learn how to use XLOOKUP today

We have loads of free resources to help you on your journey learning Excel

New to Excel? Check out our Ultimate beginner Excel Guide here.

New to Excel Power Tools? Check out these amazing Power Pivot, Power Query and Power view demonstrations

New to DAX for Power Pivot and Power BI? Let us help you get started

HAVE YOU TRIED OUR FREE TRAINING?

We reward YOU for learning Excel

NOW UPDATED WITH XLOOKUP –

The Ultimate Excel 365 Formulas course

Click to preview and explore the course more

 

Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox with Earn and Learn activities.

SIGN UP NOW

Now there is value in Learning with The Excel Club and our Learn and Earn activities.

We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn by using cryptocurrency and blockchain technology

To Find out more now and start earning while you are learning Excel and Power BI

Enjoyed this Excel Article? Before you leave, it’s only fair that you share


Cross posted from my blog with SteemPress : https://theexcelclub.com/xlookup-in-excel-removes-these-8-limitations-of-vlookup/