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

    Help on Formula (Excel 2003)

    Hi

    I need little help to understand a formula please.

    =IF(ISERROR(VLOOKUP($A5,rms,D$1,FALSE))=TRUE,0,VLO OKUP($A5,rms,D$1,FALSE))

    $A5=6621400 ,rms I understand is named range this where my understanding starts to wane, I am used to seeing
    the column number here, D$1 is a cell containing a text box.

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

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

    Re: Help on Formula (Excel 2003)

    VLOOKUP will use the value in cell D$1 as the column number to retrieve. Does D$1 contain a textbox or a combo box that allows you to select a column number? A textbox does not make much sense, but a combo box that limits the selection to the valid columns in the range rms does make sense.
    Legare Coleman

  3. #3
    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: Help on Formula (Excel 2003)

    The essential part is:
    VLOOKUP($A5,rms,D$1,FALSE)

    It looks up the value found in the cell A5. It compares that value to the upper right value in the range named "rms". It continues down the first column of the range until it finds an exact match (due to the FALSE). when a match is found it looks at the value in the cell D1 and goes that many columns over (the column it is looking up in is #1, the col to the right is 2, etc). If it gets to the last row in the range and there is no match in the first column it gives an Error (#N/A).

    Your entire formula (edited,you do not need the "TRUE" it gives true or false)

    =IF(ISERROR(VLOOKUP($A5,rms,D$1,FALSE)),0,VLOOKUP( $A5,rms,D$1,FALSE))
    If the expression gives an error (no match is found) the ISerror is TRUE and it gives a zero as a result. If a match is found then the iserror is false and it give the value it looked up.

    Steve

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

    Re: Help on Formula (Excel 2003)

    $A5 contains the value to search for.
    rms is a named range that is the range to search in.
    D$1 is the cell that contains the number of the column used for the return value. It doesn't really matter if there is a text box over the cell, the formula uses the contents of the cell.
    FALSE means that you're looking for an exact match, not for an approximation.

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

    Re: Help on Formula (Excel 2003)

    Hi Hans

    The cell D1 is blank, and so does not have a value,how does this affect the formula?

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

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

    Re: Help on Formula (Excel 2003)

    Hi Legare

    As I explained to Hans D1 only contains at text box containing the word risk.

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

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

    Re: Help on Formula (Excel 2003)

    What happens if you move the text box so that it doesn't cover D1 any more? Is D1 really blank?
    If so, the VLOOKUP function should result in an error, and hence the total formula should return 0. Is that correct?

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

    Re: Help on Formula (Excel 2003)

    I would have to see the sheet to know for sure. A cell can not contain a textbox. A textbox might cover the cell. The textbox would have to be linked to the cell to affect the formula. The textbox might have event code associated with it that sets the value of the cell depending on what is entered into the textbox. There are a number of things that could be happening here.
    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: Help on Formula (Excel 2003)

    Hi Hans

    I am sorry it looks like another senior moment I was looking at D1 in the sheet that contained the range instead of the sheet that contained the formula.

    D1 in that sheet contains 5 which thanks to you loungers I now know it means column 5 in the sheet with the rms range.

    May be this will be of help to other greenhorns like me.

    Many thanks to all who replied
    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: Help on Formula (Excel 2003)

    HI Legare

    Please see my latest reply. Sorry to waste everyones time.

    But I have learnt something new about vlookup,

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

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help on Formula (Excel 2003)

    As an aside, Vlookup will only return a #N/A error for no exact match and #REF for the column to return which is not in the range. Use ISNA instead of ISERROR if you must. ISERROR will also mask syntax errors.

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

    Re: Help on Formula (Excel 2003)

    Hi

    Thanks for that useful piece of info, I inherited this spreadsheet, so I think there may a few modifications needed.

    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: Help on Formula (Excel 2003)

    iserror won't mask "syntax errors" as formulas with syntax errors are will not be accepted as formulas.

    It will mask other errors (div/0, #value, etc) that are in teh range or in the other cells. But it all depends on what is desired: to hide all errors or only #n/a errors. #n/a errors that are not caused by "no match" will be masked if ISNA is used.

    Steve

  14. #14
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help on Formula (Excel 2003)

    Steve,

    See if Excel accepts this formula, without a named range named table and vlokup

    =IF(ISERROR(vlokup(A1,table,2,0)),"",VLOOKUP(A1,ta ble,2,0))

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

    Re: Help on Formula (Excel 2003)

    But that is not a syntax error. Syntax errors are things like missing parenthesis. If you create a function named vlokup and create a name "table", your formula will work with no errors, therefore the syntax is correct. You can not fix a syntax error without physically changing the formula itself.
    Legare Coleman

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
  •