Results 1 to 4 of 4

Thread: How Can I Use a Wild Card in a VLOOKUP Table Array?

  1. #1

    Question How Can I Use a Wild Card in a VLOOKUP Table Array?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I understand how to use a asterisk in a VLOOKUP function's "lookup_value" but I need to work it a little backwards and use a wild card in the "table_array" somehow.

    Example:

    Cell A1
    c.b.a

    Cells C1 : C3
    a.*.*
    b.*.*
    c.a.*
    c.*.a

    Cells D1 : D3
    Group 1
    Group 2
    Group 3
    Group 4

    I need Cell B1 to vlookup what's in A1 ("c.b.a") and return Group 4.

    I have about a thousand values in column A that need to each be assigned one of 50 groups (all in column D). Is there a way to use the wildcard in this backwards function?

    Thanks,
    brnielsen44

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Does this work?

    Code:
    =LOOKUP(10^10,SEARCH($C$1:$C$4,A1),D1:D4)


  3. #3
    Yes, that worked perfectly. Thank you NBVC.

    Quick follow-up... what does the 10^10 do in that function?

    Thanks again.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    It's a very large number (10 to the power of 10). LOOKUP(looks for that number, and if it does not find it, it will take the last number in the range, which is smaller than that very large number).

    SEARCH() yields numeric positional results where matches are found, and LOOKUP then finds the last number in the string of results and returns that number and corresponding D14 entry.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •