Thanks:  0
Likes:  0

1. 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

2. 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))

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

4. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•