+ Reply to Thread
Results 1 to 18 of 18

Complex Vlookup from Range

  1. #1
    Registered User
    Join Date
    03-28-2012
    Location
    Istanbul
    MS-Off Ver
    Excel 2010
    Posts
    43

    Complex Vlookup from Range

    Hi all,

    I am trying to retrieve the header value of a certain lookup value without the spaces in between. I know it doesnt make sense when I put it like this but sincerely I do not have
    much idea how to define the problem. I did try many formulas but I am completely stuck at the moment. A sample file is included which will hopefully define the problem much better.

    Any help is so much appreciated... thanks alot in advance
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-06-2012
    Location
    Erode, India
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Complex Vlookup from Range

    Hi,

    I have created a VBA for you
    Please Login or Register  to view this content.
    where I3 is the name.

    I have attached the sample sheet.

    Which will enter the details as u required for vinodh.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-28-2012
    Location
    Istanbul
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Complex Vlookup from Range

    Thank you vinodhec

    Do I copy the code to a module?

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Complex Vlookup from Range

    I used a helper column, with this formula in G2:

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


    which is copied down to the bottom of your table. Then in J2 I put this formula:

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


    which is copied across to Q2.

    Hope this helps.

    Pete
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-06-2012
    Location
    Erode, India
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Complex Vlookup from Range

    Yes you can.

    Regards,
    Vinodh.T, Erode

  6. #6
    Registered User
    Join Date
    03-28-2012
    Location
    Istanbul
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Complex Vlookup from Range

    Thank you both so much Vinodhec and Pete-UK

    Perfect solutions.. Thanks again

  7. #7
    Registered User
    Join Date
    04-06-2012
    Location
    Erode, India
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Complex Vlookup from Range

    Quote Originally Posted by Pete_UK View Post
    I used a helper column, with this formula in G2:

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


    which is copied down to the bottom of your table. Then in J2 I put this formula:

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


    which is copied across to Q2.

    Hope this helps.

    Pete

    I have tried this, very simple and good solution but what if if "John" is repeated twice or thrice in same row like john appears for both monday and tuesday?

    I feel only the last occurence in the row will come in the table.

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Complex Vlookup from Range

    Try This array
    Formula
    {=IF($I3="","",IFERROR(OFFSET($A$1,0,SMALL(IF($C$2:$E$18=$I3,COLUMN($C$2:$E$18)+(ROW($C$2:$E$18)-1)*MAX(COLUMN($C$2:$E$18))),COLUMNS($J$2:J$2))-SMALL(IF($C$2:$E$18=$I3,(ROW($C$2:$E$18)-1)*MAX(COLUMN($C$2:$E$18))),COLUMNS($J$2:J$2))-1),""))}

  9. #9
    Registered User
    Join Date
    03-28-2012
    Location
    Istanbul
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Complex Vlookup from Range

    Well in my format it can only appear once in every row so no problem there ...but if someone else acts against that principle I guess it becomes a problem. And by the way
    can I ever do this without a helper column or is that my only option?

  10. #10
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Complex Vlookup from Range

    Then you can try the nice ARRy formula suggested by nflsales, replacing the typo mistake in first if..

    =IF($I2="","",IFERROR(OFFSET($A$1,0,SMALL(IF($C$2:$E$18=$I3,COLUMN($C$2:$E$18)+(ROW($C$2:$E$18)-1)*MAX(COLUMN($C$2:$E$18))),COLUMNS($J$2:J$2))-SMALL(IF($C$2:$E$18=$I3,(ROW($C$2:$E$18)-1)*MAX(COLUMN($C$2:$E$18))),COLUMNS($J$2:J$2))-1),""))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  11. #11
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Complex Vlookup from Range

    Try This array Formula
    This will work for n number of appearances in a single row and no supporting column required

    {=IF($I3="","",IFERROR(OFFSET($A$1,0,SMALL(IF($C$2:$E$18=$I3,COLUMN($C$2:$E$18)+(ROW($C$2:$E$18)-1)*MAX(COLUMN($C$2:$E$18))),COLUMNS($J$2:J$2))-SMALL(IF($C$2:$E$18=$I3,(ROW($C$2:$E$18)-1)*MAX(COLUMN($C$2:$E$18))),COLUMNS($J$2:J$2))-1),""))}

  12. #12
    Registered User
    Join Date
    03-28-2012
    Location
    Istanbul
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Complex Vlookup from Range

    Dont think that I left guys .. just trying to apply to my main project and trying to keep up.. I am still here..

  13. #13
    Registered User
    Join Date
    03-28-2012
    Location
    Istanbul
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Complex Vlookup from Range

    Thank you so much Fotis1191 and nflsales .. array works like a charm.. Much appreciated
    and thanks again Pete_UK and vinodhec... I think the problem is solved.

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Complex Vlookup from Range

    You can mark the thread as solved by clicking on Thread Tools above your first post and then Mark Thread as Solved.

    Also, you can pass on thanks more directly to any contributor that has helped you by clicking on the "star" icon in the bottom left corner of any post that you have found helpful (not just on this thread).

    I don't know why people are averse to using helper columns, though - they are there to help the solution and tend to result in a more compact solution (and can easily be hidden if you don't want to see them). My first formula has 43 characters and 3 function calls, and is copied into 17 cells. My second formula has 125 characters, 9 functions and is copied into 8 cells. The array function from Fotis and nflsales has 238 characters, 16 functions, and is also copied into 8 cells. However, an array function needs extra memory to evaluate the intermediate arrays, and as OFFSET is used within that formula (which is a volatile function), the formula will be re-calculated whenever there is a change in the file.

    Keep it Simple - easier to maintain that way.

    Hope this helps.

    Pete

  15. #15
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Complex Vlookup from Range

    Pete:

    I just want to clarify that:

    1) Array formula was not mine. Was nflsales's formula.

    2) I am big fan of helper columns. I'd like to remind you that i had start a thread about this.

    http://www.excelforum.com/the-water-...r-columns.html

  16. #16
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Complex Vlookup from Range

    Hi Fotis,

    yes, I had remembered your thread and I wasn't having a go at you.

    Thanks for the rep, by the way.

    Pete

  17. #17
    Registered User
    Join Date
    03-28-2012
    Location
    Istanbul
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Complex Vlookup from Range

    Ok I am back...

    The array formula works flawless I managed to adapt it to my main project withouth a problem.
    As for the helper column issue... since my format had been setup earlier every other column insertion between columns causes me to make quite alot of formula modifications at one point or another. Therefore, I preferred the
    non helper column solution this time. But you are both right if I was building the file from ground up I'd definitely go with less memory consuming and processor tiring solution. Lucky for me I jhave recently upgrded to
    an i7 Asus with 16 gb of 1600 mhz Ram, installed the 64 bit version of Excel 2010 so presently I can work very fluently with this file even if I use an array formula of many arguments.

    Once again I can't thank enough to all of you guys, hope I can reach your level someday
    Last edited by ant1905; 03-12-2013 at 09:42 AM. Reason: sentence correction

  18. #18
    Registered User
    Join Date
    03-28-2012
    Location
    Istanbul
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Complex Vlookup from Range

    Checking as thread as solved
    Last edited by ant1905; 03-12-2013 at 09:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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