+ Reply to Thread
Results 1 to 5 of 5

Return values to specific columns based on range of cell values

  1. #1
    Registered User
    Join Date
    05-27-2019
    Location
    Singapore
    MS-Off Ver
    15.16
    Posts
    2

    Question Return values to specific columns based on range of cell values

    Hi,

    I need to return a value (in Sheet2) - the last 6 letters of a row of cells (e.g. GroupA) if the first 2 letters (e.g. S1) is equal to the column name of Sheet2. And the value must only be returned under the correct column - if first 2 letters (S1) matches the column name.

    Please see the screenshots for the values and example output I'm looking for.

    Also attached an excel file for reference.

    Been spending all night but can't figure this out. Help is much appreciated. Thanks!
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,746

    Re: Return values to specific columns based on range of cell values

    Do you have PowerQuery in your Mac version?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    05-27-2019
    Location
    Singapore
    MS-Off Ver
    15.16
    Posts
    2

    Re: Return values to specific columns based on range of cell values

    Sadly it's not available on the Mac.

  4. #4
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    436

    Re: Return values to specific columns based on range of cell values

    What result are you want at cell A18? GroupA, GroupB or both of them? And what is the ruler?

  5. #5
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Return values to specific columns based on range of cell values

    Hi

    in A2 (sheet2) to be confirmed with control+shift+enter before to be copied across


    =IFERROR(LOOKUP("ZZZ",INDEX(IF(LEFT(Sheet1!$A$2:$D$24,2)=A$1,IF(COUNTIF(A$1:A1,RIGHT(Sheet1!$A$2:$D$24,6)),COUNTIF(A$1:A1,RIGHT(Sheet1!$A$2:$D$24,6)),RIGHT(Sheet1!$A$2:$D$24,6))),MIN(IF(LEFT(Sheet1!$A$2:$D$24,2)=A$1,IF(COUNTIF(A$1:A1,RIGHT(Sheet1!$A$2:$D$24,6)),"",ROW($2:$24)-1))),0)),"")

    Please refer to the attachment, where I left some yellow cells just for checking outputs.

    Regards
    Attached Files Attached Files
    Last edited by canapone; 05-27-2019 at 04:47 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

+ 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: 4
    Last Post: 10-01-2015, 11:01 AM
  2. Return Cell Value based on Values in Multiple Columns
    By Cardan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-13-2015, 10:20 PM
  3. Replies: 10
    Last Post: 02-19-2013, 12:05 PM
  4. How to delete specific columns based on a named range of values from a worksheet
    By dronofrio in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-12-2012, 10:56 AM
  5. [SOLVED] Return specific values based on the range in another cell
    By AndrewBondarchuk in forum Excel General
    Replies: 3
    Last Post: 11-28-2012, 12:34 PM
  6. matching values based on criteria and return values from another columns
    By lizard54 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2012, 11:29 AM
  7. Replies: 4
    Last Post: 06-28-2012, 07:08 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