+ Reply to Thread
Results 1 to 16 of 16

if criteria extract numbers in range

  1. #1
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    if criteria extract numbers in range

    in a1:a47 i put manually numbers fro 1 to 36 ,so how we can have an array formula which say:if the last number from below at a1:a47 is 9 (for example) ,extract some specific numbers which are in a range b1:b18) for example.i was thinking something like that:IF(INDEXA1:A47,MATCH(99^99,A1:A47,1))=9,INDEX(B1:B18,ROWS($1:1), i dont know how.
    Last edited by zzz444; 06-29-2020 at 03:52 PM.

  2. #2
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: if criteria extract numbers in range

    in the attached file i use a formula that extracts bi:b18 in case when a1=9 but i the formula to take into acount the last input number of A:A becouse maybe is 10,0r 30 or any .thanks
    Attached Files Attached Files

  3. #3
    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: if criteria extract numbers in range

    Please add another column manually to show the results you desire.
    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.

  4. #4
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: if criteria extract numbers in range

    the results ai desire situeded at B1:B18 and i wand them to be extracted at c1:c18 in case that the last numbet at A"A is 9(last number from below)

  5. #5
    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: if criteria extract numbers in range

    What you have described is just this:

    =IF($A$1=9,B1,"")

    Where are the numbers in B coming from?

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    1
    9
    25
    #REF!
    2
    36
    #REF!
    3
    8
    #REF!
    4
    4
    #REF!
    5
    1
    #REF!
    6
    5
    #REF!
    7
    25
    #REF!
    8
    8
    #REF!
    9
    7
    #REF!
    10
    6
    #REF!
    11
    4
    #REF!
    12
    7
    #REF!
    13
    6
    #REF!
    14
    4
    #REF!
    15
    13
    #REF!
    16
    12
    #REF!
    17
    11
    #REF!
    Sheet: Sheet1

  6. #6
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: if criteria extract numbers in range

    yes ,someting is wrong with the previous formula

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

    Re: if criteria extract numbers in range

    What you have described is just this:

    =IF($A$1=9,B1,"")

  8. #8
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: if criteria extract numbers in range

    so the question is: if the last!! number at A:A is 9 extract the results at b1:b18 to ci:c18

  9. #9
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: if criteria extract numbers in range

    i mean the lat number at A:A ,no the A1 .at the previous formula was like example for A1,i need if the last number is 9,then extract.thanks

  10. #10
    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: if criteria extract numbers in range

    Really rubbish sample data - sorry!

    Another guess:

    =IF(LOOKUP(2,1/(A:A<>""),A:A)=9,B1,"")

  11. #11
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: if criteria extract numbers in range

    if i was a forum moderator i would not make rubbish examples,iam not so good yet..

  12. #12
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: if criteria extract numbers in range

    yes!is working!and how we modified it to write zero,so if not 9 write zero

  13. #13
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: if criteria extract numbers in range

    sorry,i think is fine like this,thanks a lot

  14. #14
    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: if criteria extract numbers in range

    Sample data needs to represent the REAL data. You gave us ONE value in A, when you really wanted us to work with a range, and a column of #REF! errors. Useless!!!

    =IF(LOOKUP(2,1/(A:A<>""),A:A)=9,B1,0)

  15. #15
    Registered User
    Join Date
    05-24-2020
    Location
    greece
    MS-Off Ver
    2010
    Posts
    76

    Re: if criteria extract numbers in range

    thanks Sweetheart..i like your new photo also.

  16. #16
    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: if criteria extract numbers in range

    You're welcome, Cheeky.

+ 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. [SOLVED] if criteria extract group of numbers
    By zzz444 in forum Excel General
    Replies: 5
    Last Post: 06-14-2020, 03:27 AM
  2. [SOLVED] Excel formula to Extract Numbers Within a Defined Range
    By Bontcho10 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-05-2019, 06:06 PM
  3. [SOLVED] Extract the minimum and second lowest numbers from an array with multiple criteria
    By Shareez Saleem in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 04-05-2017, 08:43 AM
  4. How to extract data from range to a new worksheet if it fits 2 criteria
    By DrewBoid in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 06-15-2016, 03:36 AM
  5. [SOLVED] Extract and add numbers from a text range
    By shankarnayagam in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-14-2015, 08:09 AM
  6. Extract a list of numbers under multiple criteria to another workbook
    By lks0912 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2013, 08:03 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