Code doesnt work

KnAsTa · Apr 14th, 2002, 03:20 AM

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

Aladin Akyurek · Apr 14th, 2002, 03:24 AM

Quote:

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?

KnAsTa · Apr 14th, 2002, 03:27 AM

Its a different question, the formula is different now, its shortenned and For column B not A. Sorry.

Aladin Akyurek · Apr 14th, 2002, 03:34 AM

Quote:

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.

KnAsTa · Apr 14th, 2002, 03:42 AM

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)
Very sorry for the inconvenience. And thankyou for your constant help.

KnAsTa · Apr 14th, 2002, 06:35 AM

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!

Dave Patton · Apr 14th, 2002, 07:17 AM

>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

KnAsTa · Apr 14th, 2002, 07:21 AM

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.

Dave Patton · Apr 14th, 2002, 08:18 AM

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

KnAsTa · Apr 14th, 2002, 08:25 AM

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

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

Apr 14th, 2002, 03:20 AM	#1
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 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.

Apr 14th, 2002, 03:27 AM	#3
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	Its a different question, the formula is different now, its shortenned and For column B not A. Sorry.

Apr 14th, 2002, 03:42 AM	#5
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	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) Very sorry for the inconvenience. And thankyou for your constant help.

Apr 14th, 2002, 06:35 AM	#6
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	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!

Apr 14th, 2002, 07:17 AM	#7
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	>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

Apr 14th, 2002, 07:21 AM	#8
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	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.

Apr 14th, 2002, 08:18 AM	#9
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	>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))

Apr 14th, 2002, 08:25 AM	#10
KnAsTa Board Regular Join Date: Apr 2002 Location: Australia Posts: 52	>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.