+ Reply to Thread
Results 1 to 9 of 9

Lookup when lookup value is in middle of table

  1. #1
    Registered User
    Join Date
    08-04-2006
    Posts
    55

    Lookup when lookup value is in middle of table

    Hi there

    I'm after some expert help once again. I want to know if it's possible to lookup a value that is in the middle of a table and return a cell that is in a column before the value?

    example of lookup table attached. on the example i want to be able to search on the name and return the date of input. any ideas?
    Attached Files Attached Files

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440
    Hi,

    To look up John in your example:

    =INDEX(A2:F4,MATCH(F2,F2:F4,0),1)

    HTH,

    Dave

  3. #3
    Registered User
    Join Date
    08-04-2006
    Posts
    55
    Thanks Sweep - that works great if the formula is on the same workbook. The problem I have is that I want to be able to reference from this table into another workbook, so the Match would be on something in the seperate book?

    Any further ideas?

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440
    MATCH returns a row number where its found the data you've specified in the range you've specified.

    INDEX returns the contents of a specified row/column in a range you've specified.

    When using the two in combination as I did above, the range used for the MATCH part of the function should be within the range specified in the INDEX function.

    I hope this clarifies - if it doesn't please post an example of what you want across the two workbooks and I'll see if I can get it working for you.

    Cheers,

    Dave

  5. #5
    Registered User
    Join Date
    08-04-2006
    Posts
    55
    Thanks Dave. I have attached the two example spreadsheets.

    So, in column E on example 3 I want to return the date that is in column A on example 2 for the relevant cilent number. so it should search example 2 for the client number in column D.

    The problem is, on example2, the client number is in column C, but the date I want it to return is in column A, otherwise I could just use the VLOOKUP function.

    Is that a bit more clear? It's quite a strange query!
    Attached Files Attached Files

  6. #6
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440
    =INDEX('[Example 2.xls]Commission'!$A:$E,MATCH(D2,'[Example 2.xls]Commission'!$C:$C,0),1)

    in E2 of Example 3

    HTH,

    Dave

  7. #7
    Registered User
    Join Date
    05-28-2016
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    2

    Re: Lookup when lookup value is in middle of table

    Need help urgently!!
    I have a table with invoice number, price, and payment method (cash or card). I want to create a summary of this at the bottom of my spreadsheet. Usually this would be simple because I would use index and match. However, I have multiple "prices" corresponding to the same invoice number. How do I create a summary of this such that excel finds the 2nd or 3rd occurrence of the same invoice number and returns the different corresponding value instead of always selecting the first corresponding value. To add an extra layer of complexity, the repetition of invoice numbers is not always at the beginning, and can be in the middle of the list.
    Would appreciate any EXCEL GURU's help on this!
    Cheers

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lookup when lookup value is in middle of table

    hi MARRS, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lookup when lookup value is in middle of table

    deleted forum induced duplicate post

+ 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