Ok, i have this formula, it takes data from external spreadsheets and records it in the main spreadsheet containing A5 (see below).
=IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,0)),
-- this part works, it returns true or false. Tested it.
IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),
-- this part works, it returns 1 or 0 Iff first lookup greater than second lookup.
IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)<VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0))
-- this last part does not work, and for the cells it is meant to put 1 or 0 in, it puts #N/A.
Why does this happen when we get true or false returning, and the last piece of code is exactly the same as the first piece of code, just with the > sign changed to <.
Complete code:
=IF(ISNUMBER(MATCH(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),1,0), IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)<VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0))
All the ranges are correct also.
Any help would be greatly appreciated. Thanx
=IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,0)),
-- this part works, it returns true or false. Tested it.
IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),
-- this part works, it returns 1 or 0 Iff first lookup greater than second lookup.
IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)<VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0))
-- this last part does not work, and for the cells it is meant to put 1 or 0 in, it puts #N/A.
Why does this happen when we get true or false returning, and the last piece of code is exactly the same as the first piece of code, just with the > sign changed to <.
Complete code:
=IF(ISNUMBER(MATCH(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),1,0), IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)<VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0))
All the ranges are correct also.
Any help would be greatly appreciated. Thanx