+ Reply to Thread
Results 1 to 3 of 3

Lookup on different columns and lots of rows

  1. #1
    Registered User
    Join Date
    11-21-2012
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    2

    Lookup on different columns and lots of rows

    This should be pretty easy but I cant find it:

    I would like to lookup the same name across several columns (about 20) and return the value of a set column for each of the found iterations.
    When there is no match, nothing should be returned.
    The number of Rows are approx 500.
    Typically the same name appears only 20 or 30 times across the entire array. So ideally, the results should be displayed on 20 or 30 rows, not on 500 rows...

    I attach a sample. Hope it clarifies.

    Thanks for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Lookup on different columns and lots of rows

    I can able to retrieve only 1 Name match per column

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

    Drag it down.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    11-21-2012
    Location
    Dallas
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Lookup on different columns and lots of rows

    Quote Originally Posted by :) Sixthsense :) View Post
    I can able to retrieve only 1 Name match per column

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

    Drag it down.

    Thanks Six Sense. To be able to find the results, I created Helper columns (in my case 20) in which a COUNTIF computes the number of iterations on a Name in a single column and then I do a
    =IFERROR(IF(ROWS($E$17:$E18)>$H$16,"",INDEX(E$3:E$8,MATCH(ROWS(E$17:$E18),$H$3:$H$8,0))),"")
    kind of formula which allows me to find all the results in the same column. I then copy-paste 19 times.

    The annoying thing is that my results are spread over 20 columns. At least I have all the results but I would like to have them displayed in a single column to improve readibility.

    I put sample in attachment. I appreciate your help.

    sample forums.xls

+ 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