+ Reply to Thread
Results 1 to 9 of 9

Vlookup/index/match to return all values that match

  1. #1
    Registered User
    Join Date
    10-08-2014
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    5

    Vlookup/index/match to return all values that match

    I have two spreadsheets first spreadsheet "A" has a common value ie: 43850431000 in column "H" as in spreadsheet "B". I want to search the entire spreadsheet "B" and if value "H" is found retrieve the invoice number in column "F" for all that match. How would I do this?

    Further, I want to retrieve the quantities and dollar amounts associated with the invoice. These would be sheet "B" columns "J" and "L".

    Thank you in advance for your help!!!

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Vlookup/index/match to return all values that match

    This is a pretty simple VBA query, but your workbook is important in the design of that macro. Can you please post it with sufficient "sample data" as to get it right the first try?

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-08-2014
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    5

    Re: Vlookup/index/match to return all values that match

    I have attached a sample. On the tab application detail is where the formulas need to be and pull from the SEP OVERSIT tab. I was unable to locate an item in "H" the searching item, that had multiple invoices related. But I am sure there are.
    Thanks for your help!!!
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Vlookup/index/match to return all values that match

    Now that we're looking at a real workbook, can you restate the need?

    1) Look at ???? for a specific value to search
    2) Search in ????? column ??? to find that value
    3) Copy ???? to sheet ????? for all matches

    ...that sort of thing.

  5. #5
    Registered User
    Join Date
    10-08-2014
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    5

    Re: Vlookup/index/match to return all values that match

    I found multiple invoices for the search and have added to this report. Please disregard previous. Thank you.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-08-2014
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    5

    Re: Vlookup/index/match to return all values that match

    I need to have tab "application detail" column "I" have a formula to find "H" on tab SEP OVERSIT and return with invoice number "F" on SEP OVERSIT TAB. Also need it to return Qty in J and $ Amount K from SEP OVERSIT.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Vlookup/index/match to return all values that match

    Try these:

    I3: =INDEX('SEP OVERSIT'!B:B, MATCH($H3, 'SEP OVERSIT'!$H:$H, 0))

    J3: =INDEX('SEP OVERSIT'!J:J, MATCH($H3, 'SEP OVERSIT'!$H:$H, 0))

    K3: =INDEX('SEP OVERSIT'!L:L, MATCH($H3, 'SEP OVERSIT'!$H:$H, 0))

  8. #8
    Registered User
    Join Date
    10-08-2014
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    5

    Re: Vlookup/index/match to return all values that match

    PERFECT!!!! Thank you so very much!!!!!!!

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Vlookup/index/match to return all values that match

    As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
    FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.

+ 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. Using Index/Match to return multiple values for one match
    By superboy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2014, 06:21 PM
  2. Replies: 3
    Last Post: 05-19-2014, 02:01 PM
  3. VLOOKUP/INDEX/MATCH to return all values that match
    By lijia00 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2014, 11:56 AM
  4. [SOLVED] Formula (VLOOKUP vs INDEX & MATCH) to return multiple values in the same column
    By wfidler in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2012, 07:04 PM
  5. Index Match or Vlookup to return values?
    By Chesney95 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-30-2007, 11:05 AM

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