Thread: Help on Formula (Excel 2003)

1. 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

2. 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.

3. 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. 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. 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?

6. Re: Help on Formula (Excel 2003)

Hi Legare

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

7. 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. 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.

9. 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

10. Re: Help on Formula (Excel 2003)

HI Legare

But I have learnt something new about vlookup,

11. 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. 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.

13. 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. 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. 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.

Page 1 of 2 12 Last

Posting Permissions

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