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,

cleach86's avatar
cleach86
Copper Contributor
Jul 19, 2022

Riny_van_Eekelen 

 

Thank you very much for your input, it is appreciated. 

 

I have got the filter function to work using the serial number and it is returning the values that I want it to show. However, this was the easy part though... 

 

Is it possible to use the filter function using "jokers", so let's say that "*" is the joker. 

 

Can I then use the filter function using 1234*5678* to return all the values containing these 8 digits in this order, eventhough they might contain a space or a hyphenation. 

 

I hope you know what I mean.

Rsartori76's avatar
Rsartori76
Brass Contributor
Jul 19, 2022

cleach86 

I believe you meant "wildcard" when you said "joker" (do you speak Portuguese?). 😉

 

Unfortunately, FILTER does not accept wildcards. You'll have to incorporate LEFT and SUBSTITUTE like in Riny_van_Eekelen first response. This will look something like this: