Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Capture No from a range (Excel 2002)

    Hi

    I am tryi to capture a cell entry from a lookup range, there will only ever be one number in the range.

    Please see attached for visual explantion.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Capture No from a range (Excel 2002)

    For example

    =INDEX(F11:F13,MATCH(9.99999999999999E+307,F11:F13 ))

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Capture No from a range (Excel 2002)

    Hi Hans

    That perfect , could I impose on you to explain the MATCH(9.99999999999999E+307.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture No from a range (Excel 2002)

    How about:

    <code>
    =OFFSET(F11,MATCH(TRUE,ISNUMBER(F11:F13))-1,0)
    </code>

    That is an array function and must be confirmed with Ctrl/Shift/Enter.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Capture No from a range (Excel 2002)

    MATCH tries to find the largest value that is less than or equal to the search value. Since 9.99999999999999E+307 is the largest number that can be entered in a cell, ANY number will do, so MATCH returns the position of the number in the range.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Capture No from a range (Excel 2002)

    HI Legare

    Hans code worked fine, I get #N/A with yours.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Capture No from a range (Excel 2002)

    Hi Hans

    Once again my sincere thanks.

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture No from a range (Excel 2002)

    You didn't hold down Ctrl+Shift when you pressed Enter to confirm the formula. Read the end of my previous post.
    Legare Coleman

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Capture No from a range (Excel 2002)

    Hi Legare

    My sincere apoliges, I don't know how I could have missed that. I must have had a senior moment.

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Capture No from a range (Excel 2002)

    Hi Hans

    I have had a spanner thrown into the works, I have been requested to add some new codes to lookup list, which are alphanumeric ie K123456,
    Can this formula be changed or do I need something totally different?

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Capture No from a range (Excel 2002)

    Try this. It is an array formula, so it should be confirmed with Ctrl+Shif+Enter, not just Enter

    =INDEX(F11:F13,MATCH(FALSE,ISERROR(F11:F13)))

    Repeat: it is an array formula, so it should be confirmed with Ctrl+Shif+Enter.

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Capture No from a range (Excel 2002)

    HI Hans

    I entered your formula, but it just returns a blank cell, I need to capture it whether it is numeric or alphanumeric.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Capture No from a range (Excel 2002)

    How about:
    =INDEX(F11:F13,IF(ISNA(MATCH(9.99999999999999E+307 ,F11:F13)),MATCH(REPT("z",255),F11:F13),MATCH(9.99 999999999999E+307,F11:F13)))

    Note: the cells F11:F13 need to be blank if you want them ignored. F12 and F13 currently have text in them which will be found, unless that is also part of the puzzle that some text string should be "ignored"?

    Steve

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Capture No from a range (Excel 2002)

    In the spreadsheet you attached to the first post in this thread, the other cells contained #N/A. The formula I proposed assumed that this was still the case. You have now changed the setup, so the assumption is not valid any more. If you restore the #N/A's, the formula will work.

    If you prefer to keep the blank cells, use

    =INDEX(E11:E13,MAX((F11:F13<>"")*ROW(1:3)))

    again, as an array formula, i.e. confirm with Ctrl+Shift+Enter.

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Capture No from a range (Excel 2002)

    HI Hans

    I have reverted back to the #N/A and enterered yours and legares formula into the attached spreadsheet but can't seem to get them to work.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •