+ Reply to Thread
Results 1 to 13 of 13

Match Values in another Column and Return Same Values

  1. #1
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 11 - MS Office 365
    Posts
    88

    Match Values in another Column and Return Same Values

    Hey guys,

    I have some recipe cards where some of the ingredients are from a central kitchen (CK).

    I want a formula (for the yellow boxes) that looks for matches in the complete CK list in column M. I'd also like any applicable Quantities and UOMs returned in the columns to the right.

    Thank you!

    Danny
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Match Values in another Column and Return Same Values

    You haven't manually mocked up what you mean. Do this for the first section in yellow, please, and attach the workbook again. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 11 - MS Office 365
    Posts
    88
    This is what the final sheet should look like.

    Thank you so much
    Attached Images Attached Images
    Last edited by AliGW; 01-11-2022 at 10:56 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Match Values in another Column and Return Same Values

    So, only items that match the list on the far right?

  5. #5
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 11 - MS Office 365
    Posts
    88

    Re: Match Values in another Column and Return Same Values

    Hi Ali,

    Yes, that's what I am looking for. I've updated the file and attached it here.

    Thank you so much,

    Danny
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 11 - MS Office 365
    Posts
    88

    Re: Match Values in another Column and Return Same Values

    Hi Ali,

    Are you able to help with my request?

    Danny

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Match Values in another Column and Return Same Values

    I haven't looked at it since yesterday - sorry. Someone else may take a look - I am short of time today, I am afraid.

  8. #8
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 11 - MS Office 365
    Posts
    88

    Re: Match Values in another Column and Return Same Values

    Ok, thanks for letting me know. I will close this thread and post again, it seems people don't pay attention to a post that has already had replies

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Match Values in another Column and Return Same Values

    No, you are not allowed to do that, sorry. It is one thread per issue only. Your having bumped the thread will have moved it back to the top of the list - bumping is allowed once a day.

    Please read the forum rules regarding duplicate posting before you break them!
    Last edited by AliGW; 01-12-2022 at 09:00 AM.

  10. #10
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Match Values in another Column and Return Same Values

    Into A18:
    Please Login or Register  to view this content.
    it is array formula to accept with Ctrl+Shift+Enter. Then drag it down to end of yellowish rows
    Into B18:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    then down.
    Into C18:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    then down.
    and recopy all yellowish cells from A/B/C into other yellow ranges.

    Check attached file.
    Attached Files Attached Files
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  11. #11
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 11 - MS Office 365
    Posts
    88

    Re: Match Values in another Column and Return Same Values

    Amazing! Thank you so much!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Match Values in another Column and Return Same Values

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  13. #13
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    271

    Re: Match Values in another Column and Return Same Values

    Non-array option for A18. Recopy to E18 and I18.
    PHP Code: 
    =IFERROR(INDEX(A$3:A$16,AGGREGATE(15,6,(ROW(A$3:A$16)-ROW($A$3)+1)/(COUNTIF($M$2:$M$35,A$3:A$16)),ROWS($A$1:$A1))),""

+ 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. Replies: 3
    Last Post: 10-11-2020, 10:00 PM
  2. [SOLVED] Comparing two columns and if there is a match then return values from a different column
    By skywalkertinks in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-26-2017, 07:18 AM
  3. [SOLVED] Match Values In Two Sheets And Return Value At The Top Of The Column It Was Matched In
    By dylanvv in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 08-17-2015, 09:27 AM
  4. [SOLVED] return multiple values in 1 column via index / match
    By Bax in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-12-2013, 06:54 AM
  5. Replies: 1
    Last Post: 07-03-2013, 12:14 AM
  6. [SOLVED] VBA code to return all values in column that match a specified value
    By twanbiz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-23-2013, 05:39 AM
  7. Match values in one column and return values from another
    By chunkysoup56 in forum Excel General
    Replies: 4
    Last Post: 08-27-2010, 11:18 AM

Tags for this Thread

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