FORMULA EXPLANATION

  • Thread starter Thread starter SSJ
  • Start date Start date
S

SSJ

The following formula is being used in spreadsheet A (Forecast) to get data
from spreadsheet B (List of Open Work Orders with various types of cost).

=IF(ISERROR(VLOOKUP($C10,WIPSUM.xls!$B$2:$AJ$2000,21)),"0",VLOOKUP($C10,WIPSUM.xls!$B$2:$AJ$2000,21))

Few questions regarding the above IF statement:

1)I think the the statement is saying that if the Vlookup creates an error,
then put a zero otherwise put the lookedup value in spreadsheet A. Is my
general understanding correct?

2) Spreadsheet B contains list of open work orders with the relevant costs.
If a work order is not listed in Spreadsheet B, then, as per the formula, it
should put a zero value in Spreadsheet A - at least that is my
understanding. However, I have noticed that it puts a fake value in
spreadsheet A and then I have to manually enter the right values. My concern
is that if I am not careful I may miss out on changing the values. How can
I fix this problem? Perhaps I should take out the ISERROR and let the
formula show the error sign and then I can go and manually enter the values
for the work orders closed.

3) The other alternative to point# 2 could be that I put in the next column
to the work order number an "O" for open, a "C" for closed and a blank if no
work order is listed. I tried modifying the above formula and using it put
it did not work:

If the work order is really open and listed in spreadsheet B, it will give
me an "O". Fine!
If the work order closed and not listed in the spreadsheet B, it will STILL
give me an "O".
If the no work orders is listed, say in C10, then it will give me a "C".

The modified formula is as follows:

=IF(ISERROR(VLOOKUP($C9,WIPSUM.xls!$B$2:$AJ$2000,35)),"C",VLOOKUP($C9,WIPSUM.xls!$B$2:$AJ$2000,35))

In Spreadsheet 'B', column B is showing work order status which is, in this
case, always an "O" for open as it does not list the closed work orders.

Thanks in advance.

SJ
 
1) Yes, you are correct.

2) There is a fourth (optional) parameter that can be used with
VLOOKUP. If it is omitted it defaults to TRUE, which means that the
lookup table is assumed to be sorted on the lookup values, and if a
value is not present then it will match with the highest value which
is less than the lookup value (imagine you have 0, 5, 10, 15 etc in
your table and you want to find 7 - this will match with the entry for
5).

However, you want the option which makes VLOOKUP search for an exact
match, so you will need to put ,FALSE (or ,0) in the VLOOKUP formula.
This will change your formula to:

=IF(ISERROR(VLOOKUP($C10,WIPSUM.xls!$B$2:$AJ
$2000,21,0)),"0",VLOOKUP($C10,WIP­SUM.xls!$B$2:$AJ$2000,21,0))

and then it should give you the correct results. Note that your "0"
returned instead of the error is a text value - you might want to
change this to just 0 (without the quotes) or to "" (appears blank) or
to some message like "not present".

3) I don't think you need to pursue this now.

Hope this helps.

Pete
 
Thank you Pete!

I will incorporate the change and see how it comes out. I am sure it will be
okay.

However, I would still want to want to put an "O" and a "C" in the next cell
after the work order so that i am aware of which work orders are open and
which are closed as they are in thousands. If you could please assist in
that.

Thanks
SJ


1) Yes, you are correct.

2) There is a fourth (optional) parameter that can be used with
VLOOKUP. If it is omitted it defaults to TRUE, which means that the
lookup table is assumed to be sorted on the lookup values, and if a
value is not present then it will match with the highest value which
is less than the lookup value (imagine you have 0, 5, 10, 15 etc in
your table and you want to find 7 - this will match with the entry for
5).

However, you want the option which makes VLOOKUP search for an exact
match, so you will need to put ,FALSE (or ,0) in the VLOOKUP formula.
This will change your formula to:

=IF(ISERROR(VLOOKUP($C10,WIPSUM.xls!$B$2:$AJ
$2000,21,0)),"0",VLOOKUP($C10,WIP­SUM.xls!$B$2:$AJ$2000,21,0))

and then it should give you the correct results. Note that your "0"
returned instead of the error is a text value - you might want to
change this to just 0 (without the quotes) or to "" (appears blank) or
to some message like "not present".

3) I don't think you need to pursue this now.

Hope this helps.

Pete
 
Ok, well you could try this:

=IF(ISNA(VLOOKUP($C10,WIPSUM.xls!$B$2:$AJ$2000,21,0)),"C","O")

which means if it doesn't exist then return a C, otherwise return O.
Is this what you want?

ISNA checks only for the error message #N/A, which is what VLOOKUP
returns if there is no match.

Hope this helps.

Pete
 
Thank you Pete!

Two points:

1) It worked, however, a slight change is needed. We need to incorporate
something in the formula that if the a cell is empty with no work order
number as yet it should return a blank. You see it is a forecast and many
lines description are there with no work order number as yet.

Currently with this formula, "C" is being returned for cells with work order
number that are closed as well as for the the cells with no work order
number in it.

2) Why did you choose ISNA instead of ISERROR?

Regards
SJ

You many times
Ok, well you could try this:

=IF(ISNA(VLOOKUP($C10,WIPSUM.xls!$B$2:$AJ$2000,21,0)),"C","O")

which means if it doesn't exist then return a C, otherwise return O.
Is this what you want?

ISNA checks only for the error message #N/A, which is what VLOOKUP
returns if there is no match.

Hope this helps.

Pete
 
So, you need to test for the cell being empty first (I presume you
mean C10?). Try this:

=IF($C10="","",IF(ISNA(VLOOKUP($C10,WIPSUM.xls!$B$2:$AJ
$2000,21,0)),"C","O"))

In answer to your 2nd question:
ISNA checks only for the error message #N/A, which is what VLOOKUP
returns if there is no match.
Click to expand...

ISERROR will trap any and all errors, whereas ISNA only checks for #N/
A - this way, if there is a different error (eg a 5th parameter in the
lookup formula) then the error will be reported.

Hope this helps.

Pete
 
It works very well. Thank you very much!

SJ


So, you need to test for the cell being empty first (I presume you
mean C10?). Try this:

=IF($C10="","",IF(ISNA(VLOOKUP($C10,WIPSUM.xls!$B$2:$AJ
$2000,21,0)),"C","O"))

In answer to your 2nd question:
ISNA checks only for the error message #N/A, which is what VLOOKUP
returns if there is no match.
Click to expand...

ISERROR will trap any and all errors, whereas ISNA only checks for #N/
A - this way, if there is a different error (eg a 5th parameter in the
lookup formula) then the error will be reported.

Hope this helps.

Pete
 
Back
Top