Problem with a formula

[KnAsTa]

Ok, right now this formula only works for the first half: (it is looking for several values in an external spreadsheet and returning the correct values. either 3 or 0)

=IF(ISERROR(LOOKUP(A5,week1.xls!$A$1:$A$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),3,0),

The second part of this formula however, doesnt work. It is returning #N/A where it should be returning another set of 3's and 0's. I had some help before on this from Ian (thanks for that) but what i used from his explination helped, but it still doesnt work.

IF(ISNA(LOOKUP(A5,week1.xls!$A$1:$D$10,4)>VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)),3,0))

So if anyone can find the problem with this code, it would be greatly appreciated. Thanks a lot

 

[Dave Patton]

With the addition of a ) at the end,
and revision of the first part of your formula, it looks OK. The brackets are incorrect at the end of the first part.

Revise the references to fit your information.

=IF(ISERROR(LOOKUP(B1,$A$2:$A$10)),"",IF(VLOOKUP(B1,$A$2:$D$10,3,0)>VLOOKUP(B1,$A$2:$D$10,4,0),3,0))

Note. With the above, if there is no value to lookup, the formula shows a blank "".

Check each part of the formula and the data to isolate the error.

Another approach is to add another column to the lookup table with =C3-D3

The formula could then be

=(VLOOKUP(B1,A1:E10,5)>0)*3

Or put all the calculations on the Lookup Table and the formula could be

=VLOOKUP(B1,A1:F10,6)<
This message was edited by Dave Patton on 2002-04-13 07:52