+ Reply to Thread
Results 1 to 17 of 17

How to return the first value in a range of cells?

  1. #1
    Registered User
    Join Date
    12-01-2020
    Location
    singapore
    MS-Off Ver
    Office 2024
    Posts
    42

    How to return the first value in a range of cells?

    Column F is exactly what I want

    Thanks in advance
    Attached Files Attached Files
    Last edited by sgstevenlow; 04-15-2025 at 09:09 AM.

  2. #2
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2503 and WPS V2024(12.1.0.18543)
    Posts
    3,944

    Re: How to return the first value in a range of cells?

    pls try this formula Cell F2 formula , Drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,054

    Re: How to return the first value in a range of cells?

    here, try this:

    =INDEX(A2:E2,SMALL(IF(ISNUMBER(A2:E2),COLUMN(A2:E2)),1))
    Never use Merged Cells in Excel

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,787

    Re: How to return the first value in a range of cells?

    A non-volatile option
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-01-2020
    Location
    singapore
    MS-Off Ver
    Office 2024
    Posts
    42

    Re: How to return the first value in a range of cells?

    WOW wk9128 , zbor and Fluff13, you are amazing, that's what it is, thanks for your quick reply

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,991

    Re: How to return the first value in a range of cells?

    Are you still using Excel 2016?
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

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

    Re: How to return the first value in a range of cells?

    For Excel 2024 or 365, in H2 followed by ENTER:

    =BYROW(B2:E10,LAMBDA(r,TAKE(FILTER(r,r<>"-"),,1)))

  8. #8
    Registered User
    Join Date
    12-01-2020
    Location
    singapore
    MS-Off Ver
    Office 2024
    Posts
    42

    Re: How to return the first value in a range of cells?

    No @AliGW , it's Office 2024

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

    Re: How to return the first value in a range of cells?

    OK - see my solution and please update your forum profile.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-01-2020
    Location
    singapore
    MS-Off Ver
    Office 2024
    Posts
    42

    Re: How to return the first value in a range of cells?

    WOW AliGW , you are amazing Thank You very Much !

  11. #11
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2503 and WPS V2024(12.1.0.18543)
    Posts
    3,944

    Re: How to return the first value in a range of cells?

    @sgstevenlow You're Welcome. Glad to help . Thank You for the feedback and rep.

    non-volatile option
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    OR OFFICE 365
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by wk9128; 04-15-2025 at 09:29 AM.

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,787

    Re: How to return the first value in a range of cells?

    Glad to help & thanks for the feedback.

  13. #13
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,054

    Re: How to return the first value in a range of cells?

    Another option for newer excel versions:

    =LOOKUP(99^99,SORTBY(B2:E2,COLUMN(B2:E2),-1))

  14. #14
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2503 and WPS V2024(12.1.0.18543)
    Posts
    3,944

    Re: How to return the first value in a range of cells?

    Another option for HLOOKUP Function
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  15. #15
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,054

    Re: How to return the first value in a range of cells?

    With FILTER and TAKE combination:

    For columns:
    =TAKE(FILTER(B2:E2,ISNUMBER(B2:E2)),,1)

    For rows:
    =TAKE(FILTER(B2:B10,ISNUMBER(B2:B10)),1)

  16. #16
    Registered User
    Join Date
    01-11-2021
    Location
    CANADA
    MS-Off Ver
    Excel 365
    Posts
    51

    Re: How to return the first value in a range of cells?

    =BYROW(B2:E10,LAMBDA(x,TAKE(TOCOL(x+0,3),1)))

  17. #17
    Registered User
    Join Date
    12-01-2020
    Location
    singapore
    MS-Off Ver
    Office 2024
    Posts
    42

    Re: How to return the first value in a range of cells?

    Hi Adtxc 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. Return multiple cells if one cell is equal to other cells in a range
    By saintvinni in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-07-2020, 07:22 PM
  2. [SOLVED] Search range of cells, return cell locations of all cells of particular colour
    By AJB611 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-03-2017, 11:50 AM
  3. [SOLVED] Return a value for finding range of cells in another range of cells
    By kuzner in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-16-2016, 07:04 PM
  4. [SOLVED] Return non-blank cells from range AND ignore cells with only formula w/o results
    By grafitti in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2014, 08:15 AM
  5. Replies: 1
    Last Post: 09-04-2013, 07:23 AM
  6. Replies: 2
    Last Post: 11-12-2012, 06:26 AM
  7. Replies: 5
    Last Post: 08-29-2012, 03:53 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