+ 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
    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,416

    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!
    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
    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
    439

    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 2019
    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