Code doesnt work

KnAsTa

Board Regular
Joined
Apr 11, 2002
Messages
52
=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),)

When the ISNUMBER(MATCH) function returns true, it gives me #N/A results in the cells which should have 1 or 0 in them.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
On 2002-04-14 02:20, KnAsTa wrote:
=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),)

When the ISNUMBER(MATCH) function returns true, it gives me #N/A results in the cells which should have 1 or 0 in them.

Just curious: Why do you need to restart another thread on the same question?
 
Upvote 0
Its a different question, the formula is different now, its shortenned and For column B not A. Sorry.
 
Upvote 0
On 2002-04-14 02:27, KnAsTa wrote:
Its a different question, the formula is different now, its shortenned and For column B not A. Sorry.

Not a good reason. If still interested, try to provide the data using the procedure I described to you in the previous post.
 
Upvote 0
Ok, next time i'll contain it to one thread. Sorry.

But my question is, in the previous formula:

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

This works fine for me.

but if u change the Range in the match function to $B$1:$B$10 it doesnt work, it gives me #N/A results.

Here is my new formula:

=IF(ISNUMBER(MATCH(A5,week1.xls!$B$1:$B$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),)

Very sorry for the inconvenience. And thankyou for your constant help.
 
Upvote 0
Could someone PLEASE help me out on the above question. Its for an assignment, and its due quite soon. It would be very much appreciated. Thanks!
 
Upvote 0
>Could someone PLEASE help me out on the >above question. Its for an assignment, and >its due quite soon. It would be very much >appreciated. Thanks!

You have asked the same question many times.

Suggestions:
- read the information in Excel's Help
- try the suggestions that people provided
- provide feedback on what worked and what did not work
- Use Search and find all postings to your question and also try search for other relevant subjects
 
Upvote 0
Yes, i have asked similar questions every time, but each time i post it is a different formula, i keep getting stuck on different parts of the formula. If someone can explain what is wrong with this formula above, why it works for A and not B, then that is all i need. This is the last part of this formula i need to know.

I have read all through the excel help pages concering the functions MATCH ISNA and the LOOKUP function, and have also searched through the majority of the LOOKUP questions on this site.

I am sorry for posting so many times on similar formulae, i am just having a lot of trouble understanding it.
 
Upvote 0
>but if u change the Range in the match >function to $B$1:$B$10 it doesnt work, it >gives me #N/A results.

>Here is my new formula:

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

Is the information in A5 in the Range B1:B10?
Your previous formula looked in A1:A10.


To access from multiple external files, consider
a)
hardcode the full address

=IF(OR(A38="",COUNTIF('E:Data02Tech02[LookupD1.xls]1'!$A$2:$F$6,A38)=0),"",VLOOKUP(A38,'E:Data02Tech02[LookupD1.xls]1'!$A$2:$F$6,6,0))

or b) if the other files are OPEN

Put reference in cell (example C37 has LookupD1.xls!rL2_ )

=IF(OR(A37="",COUNTIF(INDIRECT(C37),A37)=0),"",VLOOKUP(A37,INDIRECT(C37),6,0))
 
Upvote 0
>Is the information in A5 in the Range >B1:B10?
>Your previous formula looked in A1:A10.

The information in A5 is in the range $B$1:$B$10 which is the external spreadsheet. However it does not return my values for the true statement.

I have tried to hardcode in the address' of the external files but it is still the same. When i pasted the formulae into this forum, i had my external file open with the main spreadsheet, so it did not need a hardcoded addreess.


My external spreadsheet is this(tab delimited):

Everton Leeds 3 4
Chelsea West Ham 0 5
Southhampton Middlesbrough 2 5
Aston Villa Leicester 0 4
Liverpool Bolton 6 0
Fulham Newcastle 2 2
Sunderland Tottenham 4 3
Arsenal Blackburn 0 5
Charlton Man Utd 6 1
Derby Ipswich 3 2

And i am VLOOKING up Leeds which is A5 in my main spreadsheet. This external file contains 20 english soccer teams, playing 10 games with the home and away scores.

The formula is determining if the away team wins or loses. Ie puts a 1 for a win, or a 0 for a loss.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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