Results 1 to 15 of 19
Thread: Help on Formula (Excel 2003)

20050825, 16:49 #1
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 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
BraddyIf you are a fool at forty, you will always be a fool

20050825, 17:10 #2
 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

20050825, 17:10 #3
 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

20050825, 17:12 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050825, 17:38 #5
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 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?
BraddyIf you are a fool at forty, you will always be a fool

20050825, 17:40 #6
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 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.
BraddyIf you are a fool at forty, you will always be a fool

20050825, 17:47 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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?

20050825, 17:49 #8
 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

20050825, 17:53 #9
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 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 repliedIf you are a fool at forty, you will always be a fool

20050825, 17:56 #10
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 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,
BraddyIf you are a fool at forty, you will always be a fool

20050825, 23:30 #11
 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.

20050826, 04:25 #12
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,952
 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.
BraddyIf you are a fool at forty, you will always be a fool

20050826, 09:12 #13
 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

20050826, 09:31 #14
 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))

20050826, 12:14 #15
 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