XLOOKUP V's VLOOKUP - 8 Limitations Removed

avatar
(Edited)


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
FREE beginner excel training

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/



0
0
0.000
9 comments
avatar

Good stuff! I had a project that made extensive use of vlookup. I can't wait to try this new feature.

BTW, the newsletter sign-up page's "Sign Up Now" button leads to a bad page (the link from here works, though). I joined the site while I was there...

0
0
0.000
avatar

XLOOKUP rocks and it will make your spreadsheet using vlookup shrink a lot in size. Keey an eye out for next weeks post because I am going to post a useful video tutorial on XLOOKUP

Thanks for the heads up on the sign up button. Its seems to be working fine now....not sure what the problem was.

0
0
0.000
avatar

ACE. I am looking forward to you releasing a video tutorial on this. Will xlookup be released back to Excel 2016 or will it only be on office 365?

0
0
0.000
avatar

I believe it is not backward compatible and will only be available in 365

0
0
0.000
avatar

Paula I was wondering when you were going to inform us about this. I have seen some articles on XLOOKUP on LinkedIn and I am eagerly awaiting the roll out to all Excel 365 subscribers. Any idea when we will get it? It looks superb!!!!!

0
0
0.000
avatar

Sup @dernan, as always thanks for stopping by. I'm sure you won't be too long waiting. I will let you know when it's available.

0
0
0.000
avatar

I don't find myself using Excel as much as I used to so it's handy to read about changes, I'd never have found this out otherwise.

0
0
0.000
avatar

I am glad you found some value in it. Excel is changing a lot and for the good, stay tuned, there is more to come :-)

0
0
0.000