Thanks a ton Mr Excel for sharing Charles Williams trick. I had watched this video right after it was uploaded 3 years ago and tried it in my work. My lookup for a data set of more than 500k from lookup table of more than 500k took only a couple of seconds. Previously, I had to do a FALSE lookup in each 50k rows and copy paste the values before moving over to another 50k rows and it still till me about 45 mins.
@atExcel
8 жыл бұрын
oh... I remember this session of Charles at the Amsterdam Excel Summit. Factor of speeding up was 1,800!!! Instead of 5 minutes of recalculation it was just a few seconds. Greetings from Germany, Andreas (p. 258)
@excelisfun
8 жыл бұрын
Thanks Mr Excel and Charles "Fast" Williams!!!
@sivanandamandapati8657
3 жыл бұрын
Amazing speed observed, I am replacing all my lookup with the above logic
@DaveH3009
7 жыл бұрын
Bill, did you try LOOKUP. I tested the traditional VLOOKUP(false) and INDEX(MATCH()) combo for 7000 calculated rows with a data set of 100000, both similar at 0.5 seconds. The VLOOKUP(true) method took 0.03-0.05 seconds (similar to your result) and LOOKUP took 0.017 seconds consistently.
@DaveH3009
7 жыл бұрын
The formula I used was =LOOKUP(A2, $G$2:$G$100000, $H$2:$H$100000), I tested it again today on a larger data set and it was certainly faster than the other options. Worth giving it a try, it's an observation which may be limited to my test, I would not wish to doubt the brilliant work you and Charles do ;)
@abufun1718
6 жыл бұрын
What a nice idea! Thank you
@bharathg7284
Жыл бұрын
God bless you
@Petepall
8 жыл бұрын
Question, does the table in which you do the lookup (in the given example column G and H) need to be sorted? I used this method and had some issues in that the formula returned "Not found" although the entry to lookup was in the table. Once I sorted the table alphabetically the formula returned the correct lookup.
@Petepall
8 жыл бұрын
Thanks for the clarification.
@nikunjgattani999
8 жыл бұрын
Thanks for sharing knowledge... Do you have any video on doing vlookup using dictionary in vba
@nikunjgattani999
8 жыл бұрын
Thanks
@jonathancooper787
7 жыл бұрын
Makes me want to create a UDF. Awesome.
@JonathanExcels
7 жыл бұрын
correct me if I am wrong but...the two vlookup method must use a dataset sorted in ascending order? I started trying to cobble together a UDF and was going crazy because it was giving me the wrong answer.....until I sorted my list! Function SM_vlookup(Lvalue, Lrange, ReturnColumn, nfValue) If Application.WorksheetFunction.VLookup(Lvalue, Lrange, 1) Lvalue Then SM_vlookup = nfValue Else SM_vlookup = Application.WorksheetFunction.VLookup(Lvalue, Lrange, ReturnColumn) End If End Function
@JonathanExcels
7 жыл бұрын
Thanks Bill. I guess I knew that and missed that detail in your video.
@JayThailand
3 жыл бұрын
Does it work with using the wild cards? For an example VLOOKUP("*"&A2&"*",Table,1,True) ? Thanks Mr Excel!
@Nguroa
8 жыл бұрын
How many people wen't 'Wow' at 0.047 Sec? Very cool
@MySpreadsheetLab
8 жыл бұрын
I believe my reaction was "WWWWHAT!?!" and then "WOW!"
@alexrosen8762
7 жыл бұрын
Wow!
@samnangchhing3333
4 жыл бұрын
What about sumifs
@MrMenjol
2 жыл бұрын
It did not work for me. I just had most of the cells with the "#n/d" outcome, do not know why.
@BillJelen
2 жыл бұрын
When you switch from a VLOOKUP with ,False or ,0 as the 4th argument to this formula from Charles Williams, the lookup table has to be sorted ascending.
@ahmedal-dossary4386
6 жыл бұрын
Thanks Bill for this trick. Could you please share the macro you are running to return time taken to calculate, it’s awesome..!! And about Charles, could you please share his user / channel name on youtube.
@MrXL
6 жыл бұрын
You can find Charles Williams here: exceljet.net/people/charles-williams The macro is in this article here: docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/aa730921(v=office.12)
@Nandkishor-vf2hp
3 жыл бұрын
Veloukup kaise lagta hai
@k.k.sabariraj6484
3 жыл бұрын
Sorting by popularity ?? how we do that
@MrXL
3 жыл бұрын
That is sarcasm. Excel does not offer a "Sort by Popularity" button, hence the need to go through the steps. One way: 1. Create a pivot table showing revenue by item 2. Sort the pivot table in descending order by revenue 3. Next to the original data set, use a MATCH formula to find the position within the sorted pivot table 4. Sort the original data set ascending by location within the pivot table. Compared to this set of steps, using the formula of =IF(VLOOKUP(A2,Table,1)=A2,VLOOKUP(A2,Table,2),NA()) does not seem like a burden. Also - in case you are using Microsoft 365, you will find that the VLOOKUP function was improved in 2020 and now uses an index and is just as fast as the two-VLOOKUP formula. Upgrading to Microsoft 365 will give you the faster VLOOKUP. But if your employer is stuck in the older versions, then using the formula allows you to achieve that speed.
@k.k.sabariraj6484
3 жыл бұрын
thank you so much sir 😍 I m from India 🇮🇳 one request: please , publish this step by step procedures in one viedo it's more helpful for us thank you.
Пікірлер: 33