Excel formula help

[Kevin Rudd]

Hi

I am trying to check a cell in excel against a column and see if it is present, if it is then I want a yes or no answer.

All the values are in txt and they are not words, they are codes.

I want to see if a code from cell x appears in column y - then have an answer yes or no.

I need to then copy this formula through 7000 rows checking the next cell (below the one above) with the SAME column fields

Thanks

EggHeadCafe - Software Developer Portal of Choice
Table and Stored Procedure Profile Providers for ASP.NET
http://www.eggheadcafe.com/tutorial...a-163327f19871/table-and-stored-procedur.aspx

 

[T. Valko]

One way...

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

Copy down as needed

 

[Ms-Exl-Learner]

Use this formula in Z1 cell…

=IF(ISNA(VLOOKUP(X1,Y:Y,1)=X1),"",IF(VLOOKUP(X1,Y:Y,1)=X1,"YES","NO"))

Now copy the Z1 cell formula and paste it for the remaining cells. The
above formula will check the X1 cell value in Y Column.

If you want to check the X1 and Y1 cell values then use this formula

=IF(ISBLANK(X1),"",IF(X1=Y1,"YES","NO"))

Change the cell reference X1 to your desired cell, if required.

If this post helps, Click Yes!

 

[Ms-Exl-Learner]

A small correction in my previous formula

=IF(X1="","",IF(ISNA(VLOOKUP(X1,Y:Y,1)=X1),"NO",IF(VLOOKUP(X1,Y:Y,1)=X1,"YES","NO")))

 

[Kevin Rudd]

Thanks Biff

However how to I copy it through 7000 lines without cutting and pasting it and not dragging it down, because although it changes the cell reference automatically (required) it also moves the reverence column down one cell each time, (not required) the column reference is the same set of cells for the whole 7000 reference cells.

thanks





T. Valko wrote:

One way...
13-Nov-09

One way..

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No"

Copy down as neede

-
Bif
Microsoft Excel MV

<Kevin Rudd> wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
EBCDIC Encoding with .NET
http://www.eggheadcafe.com/tutorial...35-0e2a7d65f615/ebcdic-encoding-with-net.aspx

 

[Kevin Rudd]

Thanks Biff

However how to I copy it through 7000 lines without cutting and pasting it and not dragging it down, because although it changes the cell reference automatically (required) it also moves the reverence column down one cell each time, (not required) the column reference is the same set of cells for the whole 7000 reference cells.

thanks





T. Valko wrote:

One way...
13-Nov-09

One way...

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

Copy down as needed

--
Biff
Microsoft Excel MVP


<Kevin Rudd> wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Professional ASP.NET Web Services [Wrox]
http://www.eggheadcafe.com/tutorial...2a0-dfe7948c05a1/professional-aspnet-web.aspx

 

[Kevin Rudd]

Thanks Biff

However how to I copy it through 7000 lines without cutting and pasting it and not dragging it down, because although it changes the cell reference automatically (required) it also moves the reverence column down one cell each time, (not required) the column reference is the same set of cells for the whole 7000 reference cells.

thanks



T. Valko wrote:

One way...
13-Nov-09

One way..

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No"

Copy down as neede

-
Bif
Microsoft Excel MV

<Kevin Rudd> wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET TraceListener for Debugging
http://www.eggheadcafe.com/tutorial...0b-938c659a2076/aspnet-tracelistener-for.aspx

 

[T. Valko]

I don't understand what you mean.

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

I assume you want to check cells starting in X1. If you copy the formula
down the column then X1 will increment to X2, X3, X4 etc. The column
references will not change.

 

[ªá¥Ò¯Î]

IF(COUNTIF(A1:A15,B1)>0,"yes","no")

I don't understand what you mean.


I assume you want to check cells starting in X1. If you copy the formula
down the column then X1 will increment to X2, X3, X4 etc. The column
references will not change.

 

[Gord Dibben]

Enter the formula in A1 or whatever column is appropriate.

In Namebox type A1:A7000 and Enter

CTRL + D to copy down.

The row reference will increment but Y:Y will stay constant.


Gord Dibben MS Excel MVP