+ Reply to Thread
Results 1 to 8 of 8

Wildcards in the table_array of vlookup

  1. #1
    Registered User
    Join Date
    01-29-2014
    Location
    Maseru, Lesotho
    MS-Off Ver
    365
    Posts
    15

    Wildcards in the table_array of vlookup

    Hello,

    I am semi-familiar with using wild cards in VLOOKUP formulas. However this has always been with the ‘lookup_value’ being the item with the wild card.

    I am trying to reclassify a large data set where a range of lookup values relate to one classification and to do this I would like to use the wildcard in the in ‘table_array’ as shown below. Or said another way one value in the table array (ED.OPR.ADV.*) will be found by multiple lookup values (ED.OPR.ADV.BFM.SUP and ED.OPR.ADV.DCM.SAL).

    However when I set up my VLOOKUP it returns the value #N/A. Is there a way to perform this function or to set-up my data differently to allow this to run. I have countless permutations of ED.OPR.ADV.* in the data set and I would like to classify all as personal costs.

    Thanks,
    Jon

    Excel problem.png

    In case that image is not working I have added table to GoogleDocs: https://drive.google.com/file/d/1BCD...ew?usp=sharing
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,827

    Re: Wildcards in the table_array of vlookup

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hi sorry this function is not giving the exact required results may someone will help you

    Punnam
    Last edited by Punnam; 05-28-2020 at 01:08 AM.

  3. #3
    Registered User
    Join Date
    01-29-2014
    Location
    Maseru, Lesotho
    MS-Off Ver
    365
    Posts
    15

    Re: Wildcards in the table_array of vlookup

    Thanks Punnam,

    I have tried that and at first I thought it worked but in that formula the returns do not exactly line up as expected for instance.

    ED.OPR.LES.IT1.SAL returns 'Office' when it should return 'Personnel'
    ED.OPR.OPS.FAC.000 returns 'Personnel' when it should return 'Office'

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,104

    Re: Wildcards in the table_array of vlookup

    Please try
    at D6
    =LOOKUP(9,SEARCH($A$6:$A$13,E6),$B$6:$B$13)&""

    Vlookup with approximate match use * as a character, not a wildcard.
    G33
    =VLOOKUP(E33,SmartGrant,2)
    That why G33 return Personal from approximate match when E33 ED.OPR.GRA is not matched any value on the lookup table
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-29-2014
    Location
    Maseru, Lesotho
    MS-Off Ver
    365
    Posts
    15

    Re: Wildcards in the table_array of vlookup

    Hi Bo_Ry,

    Yes I think that has solved it.

    It will take me a while still to understand what it is that you have done and how that works but the results are all correct.

    Yes sorry E33 was an oversight on my end I was trying to simplify my example to only a few options but missed deleting that code.

    Thank you.
    Jonathan

  6. #6
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,827

    Re: Wildcards in the table_array of vlookup

    Bo_Ry
    what exactly the 9 is doing here could you please explain to me.

    Punnam

  7. #7
    Registered User
    Join Date
    01-29-2014
    Location
    Maseru, Lesotho
    MS-Off Ver
    365
    Posts
    15

    Re: Wildcards in the table_array of vlookup

    Quote Originally Posted by Punnam View Post
    what exactly the 9 is doing here could you please explain to me.

    Punnam
    Yes I am trying to understand the same.

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,104

    Re: Wildcards in the table_array of vlookup

    =LOOKUP(9,SEARCH($A$6:$A$13,E6),$B$6:$B$13)

    SEARCH($A$6:$A$13,E6) this returned array of number if text found and #VALUE! if not found

    To return 1 number from array, we can use aggregate(14, or 15 for max or min
    or lookup with large number to return last value in the array.

    SEARCH($A$6:$A$13,E6) only return 1 or #VALUE! so any number more than 1 can be use. number from 2

    eg
    lookup(2,{1;1;1,#VALUE!,#VALUE!},{1,2,3,4,5,6} ) lookup catch last number that less than 2 and return 3 from result vector

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] CONCATENATE as table_array in VLOOKUP
    By chrisboughter in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-23-2017, 09:27 AM
  2. [SOLVED] vlookup using multiple table_array
    By iamhsn in forum Excel General
    Replies: 12
    Last Post: 07-18-2012, 03:24 PM
  3. vlookup with multiple TABLE_ARRAY
    By iamhsn in forum Excel General
    Replies: 7
    Last Post: 07-16-2012, 09:17 AM
  4. table_array:VLOOKUP help
    By gigiw1986 in forum Excel General
    Replies: 3
    Last Post: 08-28-2010, 02:22 PM
  5. dynamic table_array in vlookup
    By kathy32181 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2007, 07:12 PM
  6. VLOOKUP TABLE_ARRAY
    By veljo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-31-2005, 08:06 PM
  7. Insert Vlookup into table_array of Vlookup with named range
    By Denise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-24-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1