Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 8th 05, 09:03 PM
David
 
Posts: n/a
Default how to search in excel with condition

First I must say that your site is great and it helps me a lot.

Can you explaine to me how to do search in Excel ?

explenation:

I have 2 sheets on the same excel file. I need to search one column in the
2nd sheet and if i found the data I look for then I need excel to put this
data in the first sheet on the same raw when the data is equel to.

example:
1. on sheet 1 I have names of people.
2. on sheet 2 I have a cloumn that contains some of the names from sheet 1.
3. I need excel take each raw in sheet 1 and search sheet 2 for the same
name.
4. if it found the same name in sheet 2 then it will write on sheet 1 "OK"
if it does not find it it will write "NO"

Any ideas how to do that ?

Thanks,
David

  #2   Report Post  
Old February 8th 05, 09:23 PM
Dave O
 
Posts: n/a
Default

One way to do this is using the COUNTIF() formula. Supposing your
names in both Sheet1 and Sheet2 are in column A, I wrote this formula
in Sheet1 cell B1:
=COUNTIF(Sheet2!A:A,A1)

This will return the *number* of times that the name in Sheet1 appears
in Sheet2. Is that of any use? It's more informative than "OK" and
"NO" responses. If you need the OK and NO then use this formula:
=IF(COUNTIF(Sheet2!A:A,A1)0,"OK","NO")

A caveat: If "Sam Spade" appears in Sheet1 and "Samuel Spade" appears
in Sheet2, no match will be found. Similarly, "Ed Bagley, Jr." will
not be matched to "Ed Bagley Jr" because of the punctuation difference.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Windows search in Excel with autofilter Dave Z Excel Discussion (Misc queries) 1 January 21st 05 07:44 PM
can i make a formula to search keywords in a excel workbook? gorillayam Excel Worksheet Functions 0 January 15th 05 02:25 AM
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 1 January 5th 05 08:36 AM
Excel 2000 - How to create an auto search echo7 Excel Discussion (Misc queries) 1 December 17th 04 04:32 PM
How do I get excel search through a range of numbers e.g. 1.99 - . govworker Excel Worksheet Functions 2 December 15th 04 08:47 PM


All times are GMT +1. The time now is 06:03 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017