1

I have discovered that there is a function in excel =XLOOKUP() this is a nice replacement to doing =INDEX(range_A,MATCH(value_B,range_B,0)). Does anyone know if there is a performance difference when you have 50,000 rows or more of data?

Ken White
  • 123,280
  • 14
  • 225
  • 444
Citycreek
  • 46
  • 4
  • 1
    Not sure how to answer this question but it seems interesting. – Shane S Jun 09 '22 at 00:53
  • 1
    Try it and see. Benchmark the (older,simpler,more limited) vlookup while you're at it. – Stax Jun 09 '22 at 00:59
  • 1
    @Stax - How would you do such a test? – Citycreek Jun 09 '22 at 01:48
  • You can observer CPU usage when you run `XLOOKUP()` over 50000 dataset. – Harun24hr Jun 09 '22 at 02:10
  • 1
    Simplest: Time all three with a stopwatch. if it happens too quick to be comparable, create a 500,000 dataset, or even all the rows (Approx 1 million). More accurately: you will need to Write VBA code and subtract the start time from the end time, but within Excel this will only give you 1 second granularity (actually, apparently "Timer" is ok to 1/100s. YMMV). Best accuracy (and probably far beyond your needs): https://stackoverflow.com/q/198409/3654325 – Stax Jun 09 '22 at 02:34
  • @Stax thanks for pointing me in the right direction. I also found this solution which helped be too. https://www.excelcampus.com/functions/vlookup-choose-vs-index-match/ Hey this is the first answer that I have on here can I get some upvotes? ShaneS and Harun24hr what do you think? – Citycreek Jun 09 '22 at 03:16
  • 2
    [Here's a good read](https://fastexcel.wordpress.com/2015/05/11/timing-excel-formula-calculations/) about timing Excel functions – chris neilsen Jun 09 '22 at 04:40
  • 1
    Vlookup is limited compared to index with match, so it is not a perfect replacement. – Solar Mike Jun 09 '22 at 04:44
  • @SolarMike Your right I agree with you. – Citycreek Jun 09 '22 at 04:51

1 Answers1

1

I did not create 50,000 rows of data but I was able to test it on 10,000. The difference in =XLOOKUP() and =INDEX(range_A,MATCH(value_B,range_B,0)) is virtually the same with in a margin of error. Stax said I should test the vlookup to so I did.

Testing on the whole column reference with 10,000 rows.

Function      time in sec    formula function
vlookup       0.045          =VLOOKUP(E6,$B:$C,2,FALSE)
index-match   0.044          =INDEX($A:$A,MATCH(E7,$B:$B,0))
xlookup       0.045          =XLOOKUP(E7,B:B,A:A)

Testing on only 500 rows and not whole column reference.

Function      time in sec    formula function
vlookup       0.047          =VLOOKUP(E6,$B$6:$C$505,2,FALSE)
index-match   0.047          =INDEX($A$6:$A$505,MATCH(E6,$B$6:$B$505,0))
xlookup       0.063          =XLOOKUP(E6,B$6:B$505,A$6:A$505)
Shane S
  • 1,747
  • 14
  • 31
Citycreek
  • 46
  • 4
  • Interesting! I would not have thought the first two were so similar. Not surprised the Xlookup is a little slower... it just has much more functionality. – Stax Jun 09 '22 at 23:23