+ Reply to Thread
Results 1 to 10 of 10

Find matched value of column B in Column A how to do this with formula?

  1. #1
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    365 with Dynamic Arrays
    Posts
    90

    Find matched value of column B in Column A how to do this with formula?

    Hello Experts,

    I have the attached solution with UDF but my boss does not want to enable macros, as he is scared of macros . so, we recently upgraded to Office 365 which we have dynamic arrays.

    I was asked to find a solution to use excel built in formulas instead of the UDF. how can i get the same result with built-in Excel formula?

    Thanks.

    best regards,
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Find matched value of column B in Column A how to do this with formula?

    I cannot look at your file because I'm currently writing this from a phone but simply based on the title of this thread if you want to look up a value in one column based on a source value in another column simply use the vlookup function. That'll work just fine as that's its primary purpose anyway

  3. #3
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    365 with Dynamic Arrays
    Posts
    90

    Re: Find matched value of column B in Column A how to do this with formula?

    Thanks Adam. but the vlookup does not work on this scenario.

  4. #4
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Find matched value of column B in Column A how to do this with formula?

    Well then if that's the case I can't help anymore unless you give plenty more details because it sounds to me like the function would work just fine. It works for any number of columns and any value you can type in and the fourth argument is optional

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,808

    Re: Find matched value of column B in Column A how to do this with formula?

    Adam,
    When you look at the file, you'll see why VLOOKUP will not work. He's looking for names in a comma delimited list within another comma delimited list.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Find matched value of column B in Column A how to do this with formula?

    1. Please update your profile to indicate that your are currently using 365. This will make a difference in solutions offered in the future
    2. Since you are using 365, are you willing to use Power Query/Get & Transform which is on the Data Tab of your version?
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  7. #7
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Find matched value of column B in Column A how to do this with formula?

    Greg,

    I stated that I was on a phone. You didn't assist, did you? If you didn't, shall I see if I can?

  8. #8
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    365 with Dynamic Arrays
    Posts
    90

    Re: Find matched value of column B in Column A how to do this with formula?

    Hi Alan,

    I updated my profile. I also can use Power query.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Find matched value of column B in Column A how to do this with formula?

    With Power Query:

    1. Bring Column A into PQ Editor as Table1. Split column into Rows.

    Please Login or Register  to view this content.
    2. Bring Column B into PQ Editor as Table 3. Split Column into Rows.

    Please Login or Register  to view this content.
    3. Merge Tables with Left Outer Join.

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    A
    B
    1
    Column A Table3.Column B
    2
    Jhon
    3
    Jimmy Jimmy
    4
    Alan
    5
    Michle Michle
    6
    Mike
    7
    Huda
    8
    Rambo Rambo
    Sheet: Sheet4
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-02-2017
    Location
    Sweden
    MS-Off Ver
    365 with Dynamic Arrays
    Posts
    90

    Re: Find matched value of column B in Column A how to do this with formula?

    Thanks a lot Alan

+ 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: 1
    Last Post: 11-20-2019, 10:58 PM
  2. Find items in a table having multiple matched values in another column
    By dabasir in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-27-2019, 03:18 AM
  3. [SOLVED] Formula about sum value if two column matched
    By karyx in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-17-2017, 04:34 AM
  4. File Open and Search column from A1:CE1. If matched than copy entire row for that column.
    By shivanisachdeva167 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2014, 01:51 PM
  5. Replies: 6
    Last Post: 01-29-2014, 12:22 AM
  6. Replies: 12
    Last Post: 12-28-2012, 07:49 PM
  7. Replies: 3
    Last Post: 08-01-2006, 08:15 PM

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