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,
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)))- 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!
cleach86 Unlike XLOOKUP, I don't think you can use of wildcards inside the FILTER function. But I may be mistaken.
However, you could filter for each of the two segments with ISNUMBER and FIND. Such a formula would then look something like this (in the file I attached you earlier).
=FILTER(B4:B8,ISNUMBER(FIND("1234",A4:A8)+FIND("5678",A4:A8,5)))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:
