+ Reply to Thread
Results 1 to 7 of 7

Lookup Multiple Records With One Criteria

  1. #1
    Registered User
    Join Date
    05-07-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Exclamation Lookup Multiple Records With One Criteria

    Dear All,

    Hi! I am trying to set up an Excel based formula, which looks up a particular "text or value" - which is repeated down a column numerous times. I realize that such an exercise is best done using a loop macro in Visual Basic, but I need a non-VB based solution.

    The attached file shows data on Sheet 1. I have three main columns, labelled, "First", "Last" and "Number". I want Excel to go down the first column and look for any number of times "john" is written and then display it outside in individual cells. The array formula I have come up with is shown in CELL E2. Similarly, F2 then picks up the corresponding value to E2.

    The difiiculty I am having is when I add new rows to the top of the sheet. Such that what used to be A1 becomes A2, A2 becomes A3 and so on...it completely throws the formulas in columns E and F off. Can someone please tell me how I can amend the formula so that when I add the rows on the top of the worksheet the array continues to work?

    Many thanks,
    Y.
    Attached Files Attached Files

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Lookup Multiple Records With One Criteria

    Can you put FILTER in first row and easily filter all JOHN names in first column?

  3. #3
    Registered User
    Join Date
    05-07-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Lookup Multiple Records With One Criteria

    Well, I see where you are coming from, but the idea is I would like to be able to move the matrix and cells anywhere on the worksheet and still have the array work. At the moment, I can move it along the worksheet horizontally, but as soon as I move the array vertically below, the formulas stop functioning.

    Thoughts? Suggestions?

    Thanks!

  4. #4
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    1,944

    Re: Lookup Multiple Records With One Criteria

    You must change the formula row(A1) to row()-1 according your situation.

    You can see sample as attatched.

    HTH,

    windknife
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-07-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Lookup Multiple Records With One Criteria

    Thanks HTH.
    Still doesn't work though. Try adding a couple of rows to the top of the worksheet. The resulting output answers (pertaining to John) get all messed up. As the matrix begins to move down the rows, any output result is completely skewed and lost.

  6. #6
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    1,944

    Re: Lookup Multiple Records With One Criteria

    Could you upload the test file with wrong situtation data ?

    windknife

  7. #7
    Registered User
    Join Date
    05-07-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Lookup Multiple Records With One Criteria

    Windknife - as per your request.
    See new attached file. Simple adding of the rows on the top, makes the formula not work. It no longer looks for John. Hope this makes it reasonably clear.
    Attached Files Attached Files

+ 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