Help...............again

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

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) -- 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)
All the ranges are correct also.

Any help would be greatly appreciated. Thanx

anno · Apr 14th, 2002, 02:29 AM

try this - it worked for me. i think you had a missing bracket and no false statement (ie. "0")

=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),0))

KnAsTa · Apr 14th, 2002, 02:33 AM

It still didnt work, it still gives me #N/A values. I really don't get it.

Aladin Akyurek · Apr 14th, 2002, 02:49 AM

Go to that "external spreadsheet", locate A1:D10, activate an empty cell, type =, select A1:D10, hit F9, copy what you see after the =-sign, and paste it in the follow up post.

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

Apr 14th, 2002, 01:45 AM	#1
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	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) -- 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) All the ranges are correct also. Any help would be greatly appreciated. Thanx

Apr 14th, 2002, 02:29 AM	#2
anno Board Regular Join Date: Feb 2002 Posts: 202	try this - it worked for me. i think you had a missing bracket and no false statement (ie. "0") =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),0))

Apr 14th, 2002, 02:33 AM	#3
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	It still didnt work, it still gives me #N/A values. I really don't get it.

Apr 14th, 2002, 02:49 AM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Go to that "external spreadsheet", locate A1:D10, activate an empty cell, type =, select A1:D10, hit F9, copy what you see after the =-sign, and paste it in the follow up post.