0

I have two tables, one with student numbers and their last, first names and the other of students first and last names.

enter image description here

I'm looking for a way to search the first table for records that contain a combination of the substring of the first name AND the substring of the last name from table 2 and return the student number from table 1 for matches. I can do this pretty easily in SQL, but in Excel, it's eluding me.

I tried an xlookup with the student name and student ID reversed from table 1, but could not get the substring to work with both inputs from table 2.

Any help will be greatly appreciated.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Erick Molnar
  • 11
  • 2
  • 1
    Please share the formula(s) or code(s) you have tried. Add the `excel-formula` and/or `vba` tags. Is the requirement only the `StudenID` column or all three columns? Do you have Microsoft 365? – VBasic2008 Jul 18 '23 at 05:35
  • Code I tried was an utter failure, too embraced to share. and, yes, Microsoft 365. – Erick Molnar Jul 18 '23 at 05:48
  • 1
    In e.g. `F3`, try `=XLOOKUP(E3&", *"&D3&"*",B$3:B$14,A$3:A$14,"",2)` and copy down. – VBasic2008 Jul 18 '23 at 05:51
  • I believe you need to use the Levenshtein calculation here. It requires programming skills. Because the names are not exact matches you need something to make the guess if this is correct or not, or which is the best match. And that is what Levenshtein calculation can do. It gies you a numeric "distance" between two strings. So comparing "ball" and "bull" is 1. So the lower the score the better the strings match.Essentially you need to compare each name with each name in the list with this and get the lowest score result. https://stackoverflow.com/questions/4243036/levenshtein-distance-in-vba – Andreas Jul 18 '23 at 09:51
  • Is that a typo for `Beverley Friedman`? Because in `Table 2` the `last name` shows as `Friendman`? – Mayukh Bhattacharya Jul 18 '23 at 11:31
  • 1
    As Mayukh commented those should not be a match unless you specifically want that to be. I believe power query allows fuzzy match, else there's a plugin to get fuzzy lookup. – P.b Jul 18 '23 at 12:09
  • @P.b the credit should go to JvdV Sir, i was not able to catch that. Sir found it actually. – Mayukh Bhattacharya Jul 18 '23 at 12:10
  • VBasic2008, the XLOOKUP works pretty well, far cry from where I was at when I attempted Xlookup. Thank you. – Erick Molnar Jul 18 '23 at 13:07

1 Answers1

0

For a general solution, one that does not depend upon a given organization of the words in the first table, or lack of errors of form, and can work when the searched text is just... text, general text, the following is a good basic approach:

=INDEX($A$3:$A$100,  XMATCH(1,ISNUMBER(SEARCH(D3,$B$3:$B$100))  *  ISNUMBER(SEARCH(E3,$B$3:$B$100))),  )

Basically, it makes a test for a match of each search term and creates an array of TRUE/FALSE values. The arrays (two here) are multiplied which firstly coerces those values to 1/0 values and secondly performs the multiplication. XMATCH then looks for a value of 1 in the resultant array. (MATCH produces the wrong value.) Used in the timehonored way with INDEX you get the desired value. (SEARCH will give a positional value for strings that are matched, and an error (see below) when there is no match. That's the basis for the tests.)

It will produce an #N/A error for a case of no proper match for a pair of search terms. One can handle that as fits circumstances.

(Comments seemed to have taken a turn suggesting partial or "fuzzy" matching is actually desired. This formula will not help in the least with that. It COULD be used to take out the "low hanging fruit" and have more formula afterward (IF-THEN logic) that tries other approaches for the failures. That would cut way down on calculation time, perhaps.)

Jeorje
  • 1