MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

"If/Then" statement in Excel

  Asked By: Raju    Date: Feb 07    Category: MS Office    Views: 2702

I thought I knew how to do this in Excel, but apparently not.

All I'm trying to do is write a simple (or at least it seems like it
should be simple to me) "If/Then" statement in Excel.

Here's what I'm working with.

I have two columns of data, lets call them column D and column F.
Column D has about 600 different values in it. Column F has about
3000 or so. I want to test each of the values in column F to see if
any of them are contained in column D.

Since I need to hold the range constant, I thought the forumula
was ...


I thought the above statement would have Excel syntax something
along the lines of the following statement: "If the value of cell F9
is found in any of the cells between cells D9 and D608, then
say "true" and if not, say "false"."

Apparently not so.

What I'm trying to do in the forumla above is to see if the value in
cell F9 is the same as any of the values in the range of cells from
D9 through D608. Then I want to use the same forumla to test cell
F10 against cells D9 through D608, then cell F11 against D9 through
D608 and so forth.

I have some kind of a minor error in syntax in the formula, since
the formula is only testing cell F9 against cell D9.

I thought I'd specified the range correctly, and have
looked in the Help section, to no avail. I'm using an older version
of Excel (from Office 97).

Someone else told me I need to use an Array formula instead of an
If/Then statement to perform the above function. I'm not familiar
with Array formulas, or Lookup tables either for that matter, though
I'm sure there has to be an application in Excel that will do what I
want it to.

I'd just like to have a solution to what seems to me like it should
be a simple problem.

Any ideas?



10 Answers Found

Answer #1    Answered By: Julian Long     Answered On: Feb 07

I'm more newbie than expert - but this *might* work:

dim Drow
dim Frow

For Drow = 1 To 608
For Frow = 1 to 3000
IF cells(Drow, 4).value = cells(Frow, 6).value Then
'record the dupe somewhere - in this case the next cell over from
the Drow
cells(Drow, 7).value = "True"
Next Frow
Next Drow


This will let you know which values from Column D appear in F, but
will overwrite multiple occurances, so you'll need to wtreak it if
you need that info.

Answer #2    Answered By: Omar Walker     Answered On: Feb 07

Try using this formula in cell G1, then copy it to all rows below. It
does a lookup of the value from column  F in column D, then checks to
see if it is N/A (meaning not found in column D). The ISNA function
will return true if not found. You want the opposite, hence the NOT


Answer #3    Answered By: Bonnie Hughes     Answered On: Feb 07

You must to write  the formula:

Formula 1

True and False without “”

and then, before to press Enter, press Ctrl+Shift+Enter in the cell with the
formula. Excel will automatically add the braces.

Formula 2

If you write the Formula 1 the result will return True if F9 has the same value
that any value in the range $D$9:$D$608 and the other hand this will return

Answer #4    Answered By: Percy Morgan     Answered On: Feb 07

you don't need "if/then", simply use the VLOOKUP function. Check out
Excel's help on how to apply it. If you still any help, then let me

Answer #5    Answered By: Aaron Kennedy     Answered On: Feb 07

Okay, well I figured out how to take care of the earlier question I had about
the Vlookup formula, and you're right ... so that's all taken care of.

I have another problem that I could use some help on however.

The file I'm using the Vlookup formula in is about 8.4 MB in size. When I try
to do sort functions, and sort several thousands of rows of data, I get a
message back saying the clipboard cannot be opened. If I sort a smaller portion
of the data  -- say 3000 rows or so -- and go to save it, I get a message that
comes back saying Data Not Saved.

Can someone tell me what the problem is I'm having here?

I have 224 MB of RAM and a 40 GB hard drive of which only 6 GB or so is being
used. I'm running Windows XP Home Edition and am using an older version of
Excel -- from Office 97.

Also, and this is really frustrating too, but every 15 minutes or so when I'm
working on this spreadsheet I have a window that pops up saying something like
"Microsoft Excel has encountered a problem and needs to close." Needless to
say, I always lose my unsaved data.

Any suggestions?

Answer #6    Answered By: Ana Silva     Answered On: Feb 07

I'm not sure if this is what you need but you could try selecting the
bit you want in the htm file - you'll have to do this with the mouse -
and then hitting Ctrl C for Copy and then pasting it into Notepad and
save it. See what you get. You may be able to import *that* file into

A more complex solution could be to use ADO but I'm not sure about

Answer #7    Answered By: Dustin Dean     Answered On: Feb 07

I know how to copy and paste the data  from my Yahoo message boards into Excel
and that does work. The problem is it's very tedious and time consuming to do
that page by page. So, I thought there has to be a better way to do this and I
was wondering if the Excel import/export or one of the data management functions
would accomplish what I'm trying to do so that I could just hit a button and
send the data automatically to my spreadsheet and not have to do all the copying
and pasting.

Anyone have any ideas on this?

Answer #8    Answered By: Ruairidh Anderson     Answered On: Feb 07

If you follow the export link then it's only one page you need to

If you want to automate it then (works in 2000 onwards, not sure
about 97):

go to data->Get external data->New web query

then browse to the *export* page of the yahoogroup member list and
choose the radio button for 'tables only'.

Then save the query as an .iqy file.

repeat this process for each group.

Run the query, and XL will open the web data  in a weird format:

-save this file as a tab delimited .txt file.

close workbook

open the .txt file as a comma delimited file with ' as a text

Then just delete the first character in column  A, and the last
character from column I and your done.

Answer #9    Answered By: Jay Richards     Answered On: Feb 07

go to members page

press 'export' link

wait for page to load

press crl-A to select all the text

open notepad

press ctrl-V to paste

save as .csv

import the .csv into Excel.

Answer #10    Answered By: Wade Jordan     Answered On: Feb 07

once you've saved the notepad file as a .csv it'll open in Excel just
by clicking the file.

Didn't find what you were looking for? Find more on "If/Then" statement in Excel Or get search suggestion and latest updates.