Forum Discussion

cleach86's avatar
cleach86
Copper Contributor
Jul 15, 2022
Solved

Vlookup, or alternative

Hi,    I am looking to create a lookup function for a large datasheet probably 120k rows and 10 columns (roughly).   I am looking to lookup using an order number or a drawing number. Sometimes we...
excel
Formulas and Functions
office 365
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jul 15, 2022

    cleach86 Perhaps like in the attached file. The formula used is:

     

    =XLOOKUP(D2,VALUE(LEFT(SUBSTITUTE(DrawingNrs," ",""),8)),OrderNrs,"Not found")

     

    where DrawingNrs and OrderNrs are named ranges.

     

    Cannot predict, though, how this will perform on 120K rows,

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 19, 2022

cleach86 

You could exploit the wildcard capability of either COUNTIFS or SEARCH to identify the records to retain.  SEARCH (or FIND) is more straightforward.

= FILTER(Table, ISNUMBER(SEARCH(findText,DrawingNrs)))

cleach86's avatar
cleach86
Copper Contributor
Jul 19, 2022
Thank you all for your input!
I got it to work with the wildcards using Peter's method.

This is going to be a great tool that i can start testing now. I don't think that there will be other nasty surprises to take into account.

Thanks again all of you, I hope that you have a nice day!

Resources