Thanks:  0
Likes:  0

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

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

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

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

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

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

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

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

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

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

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

## 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
•