Forum Discussion
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 use the drawing number to find the order number and vice versa.
The order number consists of 6-10 consecutive digits without spaces. (so this is not a problem)
Drawing numbers consist of 8 digits, a space and a hyphen and number.
examples:
12345678 (this is how we would type the value in)
1234 5678
1234 5678-0
1234 5678-1
1234 5678-2... etc
We usually write the drawing numbers like the first example in the list, but it could be registered in any way I've shown above and the last digit behind the hyphen is not always known.
How should I tackle this issue? Is it possible to do with vlookup, or is it a lot more complex than what this function can do?
Any suggestions are much appreciated!
Thanks
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,
10 Replies
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,
- Hi,
This seems to be worked just fine! (the sheet actually contains 140k rows) Thank you very much for your help!
I made a small modification to the formula so that I reference to a column on another tab, rather than the name of a column.
Another quick question that you might be able to answer.
What will happen in case of duplicates with this formula?
I am a little unsure if there are any, but just incase there are...
Thankscleach86 XLOOKUP, similar to VLOOKUP returns only the first match it finds.
Since you are using Office 365 you can use FILTER to return all matches to one or multiple criteria. Not sure though how exactly that could be implemented with your specific data.
- Hi Riny,
Thank you very much for your suggestion!
I will give this a go and let you know how I get on 🙂
Appreciate your input!
