Microsoft Excel help

[stewartd14]

I am trying to make a horses to follow list on MS Excel. I am importing an A-Z of runners into the document and trying to do a match up formula if you know what I mean. e.g. IF(A1=sheet2!A5,"YES","") or something like that. However due to there being over 200 horses most days what I want to know is: Instead of doing a formula for IF A1=A2,A3,A4,A5. Is there an OR formula I can use to work out if a cell equals any of the cells in a large range. Help much appreciated as I dont want to type out this long list of 200 or 300 cells with commas. I tried doing this with dragging down through the range but I assume this checks if the 1 cell equals all of the selected cells. I hope you understand and I appreciate any help from the computer geniuses on here. Thanks

[chiqo]

Don´t understand this .. sry

What would you do with this list? You wanna evince if a horse is in the race or what?

Your formula IF(A1=sheet2!A5,"YES","") shows the content cell A5 on sheet2 if there is a "YES" in this cell.If not there´s nothing in.

This kind of formula isn´t possible with if/then

Instead of doing a formula for IF A1=A2,A3,A4,A5

Because you built the form only with 1 operation .. I think ..

[GotaFancy?]

So if a horse in one sheet is listed in another sheet, then you want to say "Yes", otherwise leave it blank?

Looks like a vlookup may be your best bet, combined with an ISERROR something along the lines of:

=IF(ISERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),"","YES")

If your list of horses is:

B

G

H

Then this gives this output:

Horse
A
B	YES
C
D
E
F
G	YES
H	YES
I
J
K
L

Is that what you're after?

[stewartd14]

Precisely Gotafancy. An excellent help m8. However, I do not understand how to change the formula into the range I have got. If I had a list of say 300 horses on sheet 1 from cells A2:A301. I then have a list of all the runners on sheet 2. Say cells A1:A1001. How would the formula be. If you do not understand can you please give me an email address and I will send you a copy of the document. Many, many thanks once again.

[GotaFancy?]

=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$1:$A$1001,1,FALSE )),"","YES")

I think if you put this in cell B2 of Sheet 1 it should work (and copy it down to cell B301).

You can see my email address to the left (MSN contact - dont use MSN much, but do use the email addy)

[stewartd14]

Excellent. You have been a great help Gotafancy and the formula is now working fine. Many thanks.

[chiqo]

I´m interessting in the operations "VLookup" and "ISERROR" .. what they do?

thanks

[GotaFancy?]

VLOOKUP (Vertical lookup) looks up a value from within a list.

So VLOOKUP(A2,Sheet2!$A$1:$A$1001,1,FALSE)

says to find the value in cell A2 from within the list in the range A1 to A1001, returning the first column (which is itself - column 2 or larger would return something different, according to the range it is looking up from). The FALSE on the end just tells it to find the exact value (and not the "nearest" value).

ISERROR(blahblah) basically asks the question, is "blahblah" correct or do we have an error. It returns either TRUE or FALSE. So with the example of the VLOOKUP, if it cannot find the value it will return an error (#N/A), if it can find the value, it returns the name of the horse.

So the statement is (in english) If the horse we're looking for is not in the list (an error is returned) then display nothing ("") otherwise display "Yes".

Generally I find the Excel help very good - it will probably explaing ISERROR and VLOOKUP far clearer than I have done :ok