Broken formula

KnAsTa · Apr 14th, 2002, 12:49 AM

Ok, vlookup again:

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

This first half works. Returns 1 and 0.

IF(VLOOKUP(A5,week1.xls!$A$1:$C$10,3,0)
The second half does not work, returns #N/A.

Still really confused. The first part only worked once i had put in the second half of the equation. Without the second part it gave me 1's and 0's which made no sense.

Can someone please explain this or show me the correct code.

KnAsTa · Apr 14th, 2002, 01:00 AM

Sorry, scrap that question, now i need to know how to use a for loop in a formula.

=IF(ISNA(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,1))),IF(ISNA((VLOOKUP(A5,week1.xls!$A$1:$C$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0))),1,0),IF(ISNA(VLOOKUP(A5,week1.xls!$A$1:$C$10,3,0)
How would i get it to loop 19 times, ie, from week1.xls to week19.xls (19 external files)

Thanx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 14th, 2002, 12:49 AM	#1
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	Ok, vlookup again: =IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,1)),IF(VLOOKUP(A5,week1.xls!$A$1:$C$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0), This first half works. Returns 1 and 0. IF(VLOOKUP(A5,week1.xls!$A$1:$C$10,3,0) The second half does not work, returns #N/A. Still really confused. The first part only worked once i had put in the second half of the equation. Without the second part it gave me 1's and 0's which made no sense. Can someone please explain this or show me the correct code.

Apr 14th, 2002, 01:00 AM	#2
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	Sorry, scrap that question, now i need to know how to use a for loop in a formula. =IF(ISNA(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,1))),IF(ISNA((VLOOKUP(A5,week1.xls!$A$1:$C$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0))),1,0),IF(ISNA(VLOOKUP(A5,week1.xls!$A$1:$C$10,3,0) How would i get it to loop 19 times, ie, from week1.xls to week19.xls (19 external files) Thanx