7 Essential Habits of VLOOKUP Pros
Vložit
- čas přidán 19. 07. 2024
- VLOOKUP and XLOOKUP: we cover everything you need to know about these two Excel functions and when you should use them. DOWNLOAD THE WORKBOOK ► www.excelcampus.com/functions...
VLOOKUP vs. XLOOKUP - What's the difference (Index Match!)
In this video, we look at the key differences between Excel's XLOOKUP and VLOOKUP. We show you how to differentiate between the two, and discuss whether VLOOKUP is extinct…or just better suited to certain versions of Excel.
🔗LINKS
🔎 Get free weekly Excel tips: www.excelcampus.com/newsletter/
💡 Free 60-minute Excel training session: www.excelcampus.com/blueprint
📖 Join Our Comprehensive Excel Training Program: www.excelcampus.com/elevate
Related Content:
✅ XLOOKUP Explained in 3 Minutes: • • Xlookup In Excel: Explained In 3 Minutes
✅ XLOOKUP Compared to VLOOKUP & INDEX MATCH in Excel: • • New Xlookup Function: A Vlookup Compa...
✅ VLOOKUP - Everything You Need To Know: • Excel Vlookup Tutorial - Everything Y...
✅ INDEX MATCH Explained (An Alternative to VLOOKUP): • • How To Use Index Match As An Alternat...
00:00 Introduction
00:38 Drop The Anchor
02:15 The Last Argument
03:09 The Invisible Error
04:22 Naked VlookUps First
05:44 VlookUp os Index Match or XlookUp
08:28 Keyboard Shortcuts
09:20 Avoid Formula Stuffing
Thoughts/comments/suggestions/feedback? Leave them in the comments below ⬇️
I always use tables, that way if the data grows, the formula doesn't need changing. If you only reference and lock in certain cells, the formulas will always need updating instead of being automatic. Could you teach people to use tables instead of using ranges?
Great point! I don't always recommend tables, especially for beginners. I'm working on a video where I will explain why in more detail. Stay tuned. 🙂
Great tips. Thanks. Curious if you use text to columns. That’s my usual fix for messy data.
Great video Jon, thanks.Paul
Thanks for this excellent video.
This video came at a correct time when I was revising my LOOKUP functions in Excel.
My favorites are the Invisible Error and the usage of IFERROR outside the VLOOKUP function.
Kudos to all your efforts!!! 👍
You're very welcome! 😀
Outstanding!! 😊 thanks so much!
You are so welcome! 😀
If the retrun value column is variable (dependant on another parameter) I think a 2 dimensional vlookup using match to find the column position by its heading is a simpler formula than either the 2 dimensional XLookup of the 2 dimensional Index Match. The limitation being that it does need the vertical lookup be a column to the left of the column with the return value.
Great points! So many different ways to get the same result in Excel...
Thanks Alex! 🙂
Great video - thank you! I do have one question -- there are many times I have to use multiple columns from a data range, is there an easier method or formula to grab those additional columns other than retyping the VLOOKUP formula with same info except the new column grab? EX: =VLOOKUP(A2, Data, 2, FALSE).......then on the next column retyping =VLOOKUP(A2, Data, 3, FALSE) and so on
You may try the COLUMNS() function to count the number of columns relative to a fixed reference (orientation) cell address.
Departing from your example in your comment, sth. like this maybe helps:
*=VLOOKUP($A2 , Data_Table, COLUMNS(Absolute_RefCell_Address:Current_Cell_Address), FALSE)*
Ex: *=VLOOKUP($A2 , Data_Table, COLUMNS($A$2:B2), FALSE)*
Alternatively, you may also use the function COLUMN() without any argument (any parameter inside) to get the current cell's column number (the current cell = the cell where you write your formula/function) and then again COLUMN(Absolute_RefCell_Address) to get that of the reference cell, and calculate their difference. Like this:
*=VLOOKUP($A2 , Data_Table, COLUMN()-COLUMN(Absolute_RefCell_Address), FALSE)*
Ex: *=VLOOKUP($A2 , Data_Table, COLUMN()-COLUMN($A$2), FALSE)*
_You may have to add or subtract 1 from the difference calculation to account for the correct offsetting._
Hope this helps you!
Thank you for sharing this video
You're welcome! 😀
Jon, one more keyboard shortcut I would suggest @9:08 is to use Ctrl+Enter instead of Enter then you can fill down without needing to activate the cell again. Is there a keyboard shortcut for fill down?
Great suggestion! 🙌
thank you
Excellent video, thank you!
Thank you! 😀
Dear Jon, Nice video on vlookup! my question is when you working on large data on large sheet ! how to count column index number and use in vlookup formula? like example column 300+?
Great question! At 8:53 in the video I share a tip to find the column index number. This tip will work when you have a table array with a lot of columns. I hope that helps. Thanks and have a nice day! 🙂
Do you use a Surface Laptop to work swiftly on MS 365 essential apps like Word, Excel, PowerPoint, OneNote, etc.? If not, which one would you recommend?
XLOOKUP ftw!
Thank you very much but one question why you are not selecting as single look up value rather than all look up value at once so the result will spill...Is there is any drawback of doing so..
Great question! The main reason is compatibility. Not all viewers of the video or users of the file will be on a version of Excel that supports spill ranges.
For me, it also depends on how the sheet will be used. If users will be adding values to the bottom of the range that contains the vlookups, then I probably won't use a spill range. I might use an Excel Table, which also doesn't support spill ranges. So there are several factors that might play into that decision.
I think it's a great question and we'll add it to our list for future videos.
Thanks again and have a nice day! 🙂
If we are having huge data, which will process data faster in your example 7 formula and what impact has on the total file size i.e. using one formula stuffing or multiple columns break down of the same formula.
Great question! In this specific scenario the help columns would probably calculate faster because the vlookup used for P0 is used twice in the "stuffed" formula.
P1+D1-P0/P0
When using helper columns, we can use the result of the vlookup multiple times in the Return % formula that just does the math. Excel will not have to calculate the vlookup two times. Instead, it's using the result of P0 as a variable in another formula to improve efficiency and readability.
This would be similar to using the LET function and creating variables for the result of each vlookup formula.
I hope that helps. Thanks again and have a nice day! 🙂
I will learn let function
I understand the difference in versions that people might have available to them, but it really hurts not using tables and the new array formulas (and named functions-no VBA needed!). Using these, I've been able to do things with more than decent performance that I'm not even sure how to approach with only methods from before the shift to the array-processing engine.
Now if I could simulate a slicer with array results from (in particular) a FILTER function. Would be fairly simple IF Microsoft either added an array-based SLICER function or tied array results to a source for a table. Maybe Power Query could be leveraged somehow, but how slow would that be? [PQ is great when you aren't time constrained because it's faster than most any other way working with external sources.]
Great points, Jack! I agree that the new dynamic array functionality is amazing. I'll do more videos on those in the future.
There are some ways to "hack" slicers with FILTER and spill ranges, but I agree that it would be nice to have native functionality in Excel.
Thanks for your comments! 🙌
I find most Excel trainers (including Jon) are not discerning with the use of relative references. There's no need to apply full-absolute in most of these examples. For me, $-signs are noise, making formulas harder to read and so I use discernment. If a formula is dragged down but not across, anchor only the rows. And vice versa.
And, IfIwriteasentencewithoutspaces,it'shardertoread,right? Then why do it with formulas?
Hey Paul, I think it depends on the scenario, but in general, I disagree. I'd prefer to future proof the lookup formula to prevent maintenance and errors.
With a lot of vlookups there is a good chance that the formula will be copied to the right. In this scenario, we might want to copy the formula to the right and then change the column index number to return the company name.
Now, to fully future proof it, we would want a mixed reference for the lookup value to anchor the column. I didn't cover that in this video, as I wanted to focus on the essential habits that will prevent the most common errors. Not anchoring the table array is a very common error.
But IMO, there is not a huge difference in the readability in the following two references.
F$5:H$21
$F$5:$H$21
I get your point on the spaces and it's something I'll try to cover in a video in the future.
Thanks again and have a nice day! 🙂
@@ExcelCampus I find preciseness is important for documenting what a formula does. If it's just columns anchored or just rows anchored, it tells me how the cell references are being used. Blanket absolute references can conceal this important information, and may take longer to interpret. I think it's more advanced use of the medium to be discerning with the use of $.
Why there r still people using vlookup and hlookup?
Great question! Here is a video where I explain that in detail. czcams.com/video/CQAhPRCAjA8/video.html
There are still a lot of reasons to know, learn, and use vlookup. I think it's important that every Excel user knows when and why.
I hope that helps. Thanks! 🙂
Excel pro 😂