Why doesnt this formula work!!!????

KnAsTa

Board Regular
Joined
Apr 11, 2002
Messages
52
=IF(ISNUMBER(MATCH(A6,week1.xls!$A$1:$A$10,0)), IF(VLOOKUP(A6,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A6,week1.xls!$A$1:$D$10,4,0),3,0),

--OK, the first part works( i am getting values from an external spreadsheet), it returns the correct numbers (either 3 or 0)

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

--HOWEVER, this part does not work, it returns #N/A, and i really do not know why. I have spent the last hour trying to work it out. Can somebody please help! The formula is now a mess in my head :(

Thanks a lot
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hello,

Your second formula doesn't work because you did not assign a Value_if_true not a Value_if_false. You just typed the condition.
I mean, you did sth like this:
If x>y then.... you did not write anything.
a if funtion looks like this:
if(condition,valueIfTRue,ValueIfFalse). You only wrote if(condition).
Bye,
Pierre
 
Upvote 0
ok, i can understand that, but how do i write the code in for it. I don't get where it goes or what i should write in. Sorry :S.
 
Upvote 0
IF(ISNUMBER(MATCH(A6,week1.xls!$A$1:$A$10,0)),IF(VLOOKUP(A6,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A6,week1.xls!$A$1:$D$10,4,0),3,0),"Something here if the first part is false")
 
Upvote 0
ok, but wasnt my second part, the If statement, the False return? Can you use an If statement in the false return?
 
Upvote 0
Like this?

IF(ISNUMBER(MATCH(A6,week1.xls!$A$1:$A$10,0)),IF(VLOOKUP(A6,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A6,week1.xls!$A$1:$D$10,4,0),3,0),if(1=1,"Yes","No"))
 
Upvote 0
If I need to make a complicated set of nested if's. I find it easier to make all the components along a row and make one formula to make use of the components.
Then, one by one, I replace the cell references with the contents of the referenced cell (without the = sign of course). This way I have developed formulae which have been virtually impossible to comprehend manually. Give it a go, you never know it may help.

Good luck
 
Upvote 0
ohhhh, yeah that would help. Would you be able to give me an example of the setting out of that?
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top