Forum Discussion
Find according to Reference
Hi everyone. I need some help with excel. I have 2 excel sheets, "Worksheet" and "EV List".
In EV List, I need the data for the references mentioned there. And the data is in "Worksheet". The Reference in "EV List" might not always match 100% to the "Naam" column in "Worksheet", but the number part in the reference should match.
So I need a formula which can look for the number part of the reference in "EV List" and see if it matches in "Worksheet". If the number in "Worksheet" matches with a number in "EV List", I want it marked in for example column I in "Worksheet" that it matches to a number in "EV List".
Thank you!
In B2 on EV List:
=XLOOKUP(TEXTAFTER(B2, "-"), Worksheet!$B$2:$B$1000, Worksheet!$D$2:$G$1000, "", 2)
In Dutch:
=X.ZOEKEN(TEKST.NA(B2; "-"); Worksheet!$B$2:$B$1000; Worksheet!$D$2:$G$1000; ""; 2)
HansVogelaar& JKPieterse thank you!
I also managed to use VLOOKUP to return the values in "Worksheet" to the relevant reference in "EV List".
- You can use XLOOKUP (X.ZOEKEN in Dutch Excel) where you append "*" to the first argument:
=XLOOKUP(A2&"*",Worksheet!$B$2:$B$100,Worksheet!$D$2:$D$100,NA(),2)
