Forum Discussion
Vlookup, or alternative
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 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,
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...
Thanks
cleach86 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.
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.
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)))
