+ Reply to Thread
Results 1 to 6 of 6

Multi value Vlookup in same cell

  1. #1
    Registered User
    Join Date
    04-26-2017
    Location
    Milan
    MS-Off Ver
    Office 2013
    Posts
    47

    Multi value Vlookup in same cell

    Hi,
    i'm trying to work on a way to lookup the values in a cell(comma separated) and return multi values.

    this is the formula i'm trying to use, and if i go in the formula it looks like it is find the correct values but is showing error in return

    =TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH('ENOVIA LU'!AB:AB,VLOOKUP(CALC!B4,ENOVIA!A:IJ,244,0))),'ENOVIA LU'!AC:AC,""))


    i also attached the file,Formula is on Sheet "CALC"

    my expected result in the formula would be 100,101,102,103, which corresponds to the looked up values A,B,C,D
    can anyone support me on this?

  2. #2
    Registered User
    Join Date
    04-26-2017
    Location
    Milan
    MS-Off Ver
    Office 2013
    Posts
    47

    Re: Multi value Vlookup in same cell

    file loaded TEST MULTIPLE VALUE LOOKUP.xlsx
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-26-2017
    Location
    Milan
    MS-Off Ver
    Office 2013
    Posts
    47

    Re: Multi value Vlookup in same cell

    one last comment: i have a way to do the activity in VBA, it's working but it's probably too heavy and file gets locked while processing..

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Multi value Vlookup in same cell

    In the attached copy of the file the formula uses dynamic named ranges for 'ENOVIA LU'!$AB:$AB and 'ENOVIA LU'!$AC:$AC so that the formula doesn't evaluate blanks.
    Look at the Name Manager on the Formulas tab for the Refers to: of the ranges.
    The array entered formula* reads: =TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(Enovia_Lu_Description,VLOOKUP(CALC!B4,ENOVIA!A10:IJ11,244,0))),Enovia_Lu_Code,""))
    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    04-26-2017
    Location
    Milan
    MS-Off Ver
    Office 2013
    Posts
    47

    Re: Multi value Vlookup in same cell

    @JeteMc
    you anticipated my move, i actually fixed it with named ranges and now the file looks ok, but i have an extra challenge which i'm struggling with. as you can see in the example attached the search is not looking for the exact values within commas but for example, when searching for "Action Sports" it's returning both "Action Sports" and "Sport" codes. i tried to use FIND instead of search or EXACT but with no success. any idea if there is a way to do so?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-26-2017
    Location
    Milan
    MS-Off Ver
    Office 2013
    Posts
    47

    Re: Multi value Vlookup in same cell

    #JeteMc
    thank you by the way for the support

+ 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] VLOOKUP multi cell
    By davidmg_13 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-06-2012, 12:01 PM
  2. multi cell vlookup
    By kayslover in forum Excel General
    Replies: 9
    Last Post: 03-28-2011, 05:31 AM
  3. [SOLVED] How do I do multi VLOOKUP's based on certain criteria per cell?
    By bj in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 10:05 AM
  4. How do I do multi VLOOKUP's based on certain criteria per cell?
    By bj in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  5. How do I do multi VLOOKUP's based on certain criteria per cell?
    By Milky_UK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. [SOLVED] How do I do multi VLOOKUP's based on certain criteria per cell?
    By Milky_UK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] How do I do multi VLOOKUP's based on certain criteria per cell?
    By Milky_UK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. How do I do multi VLOOKUP's based on certain criteria per cell?
    By Milky_UK in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2005, 01:05 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