Share via

Wildcard in XLOOKUP table_array

Anonymous
2023-12-13T11:30:58+00:00

Hi All,

I am looking for some guidance on my problem, as I have hit a dead end.

Basically, I just need to perform a wilcard match on the 'Table Array' instead of in Lookup value.

Below is my data set:

Row # Lookup Value Table Array RETURN_RESULT
1 FIRST_NAME FIRST_NAME FIRST_NAME
2 APPROVER_FIRST_NAME PIN FIRST_NAME
3 SUBJECT_FIRST_NAME OCCUPATION FIRST_NAME
4 NOMINEE_FIRST_NAME GENDER FIRST_NAME
5 XINCI NATIONALITY N/A
6 QSPINQ DATE_BIRTH N/A
7 NPINNUMBER PIN
8 PPINX N/A
9 OCCUPATIONAL OCCUPATION
10 OCCUPATIONAL_GTM OCCUPATION
11 OCCUPATIONAL_LEVELS OCCUPATION
12 GENDER GENDER
13 GENDER_ID GENDER
14 PREFERREDGENDERID GENDER
15 NATIONALITY_ID NATIONALITY
16 DATE_OF_BIRTH DATE_BIRTH
17 DATEOFBIRTH DATE_BIRTH
18 BIRTH_DATE DATE_BIRTH

For example, in row #2, I need the result 'APPROVER_FIRST_NAME' has the closest similarity with 'FIRST_NAME' in the table array. Hence, it should return 'FIRST_NAME' in the result column.

How could I achieve the RETURN_RESULT column? Either using XLOOKUP/ HLOOKUP/ VLOOKUP?

Cheers,
ED

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-12-14T05:07:02+00:00

    levenshtein editDistance?

    FIRST_NAME FIRST_NAME
    APPROVER_FIRST_NAME PIN
    SUBJECT_FIRST_NAME OCCUPATION
    0 1 FIRST_NAME FIRST_NAME 0
    9 2 APPROVER_FIRST_NAME FIRST_NAME 9
    8 3 SUBJECT_FIRST_NAME FIRST_NAME 8
    8 4 NOMINEE_FIRST_NAME FIRST_NAME 8
    3 5 XINCI PIN 3
    3 6 QSPINQ PIN 3
    7 7 NPINNUMBER PIN 7
    2 8 PPINX PIN 2
    2 9 OCCUPATIONAL OCCUPATION 2
    6 10 OCCUPATIONAL_GTM OCCUPATION 6
    9 11 OCCUPATIONAL_LEVELS OCCUPATION 9
    0 12 GENDER GENDER 0
    3 13 GENDER_ID GENDER 3
    11 14 PREFERREDGENDERID GENDER 11
    3 15 NATIONALITY_ID NATIONALITY 3
    3 16 DATE_OF_BIRTH DATE_BIRTH 3
    2 17 DATEOFBIRTH DATE_BIRTH 2
    5 18 BIRTH_DATE FIRST_NAME 5
    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2023-12-14T00:09:38+00:00

    Hi,

    Use Fuzzy Lookup in Power Query.

    0 comments
  3. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2023-12-13T12:35:40+00:00

    Why should PIN match NPINNUMBER and not QSPINQ or PPINX?

    Also, which version of Excel do you have?

    0 comments