Results 1 to 5 of 5

Finding, searching, lookingup a value

This is a discussion on Finding, searching, lookingup a value within the Excel Questions forums, part of the Question Forums category; I have column A with 8000 text values (8000 rows) (e.g., RARUS03; RARUS43;etc.). I have column C with 1500 text ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    1

    Default

    I have column A with 8000 text values (8000 rows) (e.g., RARUS03; RARUS43;etc.).
    I have column C with 1500 text values (list of titles that may or may not include values equivalent to the text value in column A)(e.g., "Qualification of the RARUS03 Server", "Checklist for the RARUS43 Server").

    What formula can I use for each of the 8000 text values in column A to check their presence as part of the text values listed in column C?



  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    See the following setup:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    RARUS03TRUEQualification*of*the*RARUS03*Server*
    2
    RARUS43TRUEChecklist*for*the*RARUS43*Server*
    3
    RARUS44FALSEChecklist*for*the*RARUS43*Server*
    Sheet1*

    [HtmlMaker 2.20BETA] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    You can also use FIND in place of SEARCH. The difference is that FIND is case sensitive and does not allow wild card characters (per the help file).
    _________________
    Hope this helps.
    Kind regards,

    AL CHARA

    [ This Message was edited by: Al Chara on 2002-07-28 11:35 ]

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi Al Chara:

    I was wondering about your searching for the cell A1 value in the corresponding cell C1 only -- my hunch is that the search may have to be conducted in the entire list in column C -- what do you think?

    If the search has to be conducted in the entire list in column C, then one of the ways will be to use an array formula like the one in the following simulation:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    RARUS03TRUEChecklist*for*the*RARUS03*Server*
    2
    RARUS43TRUEChecklist*for*the*RARUS43*Server*
    3
    RARUS44TRUEChecklist*for*the*RARUS46*Server*
    4
    PARUS04FALSEChecklist*for*the*RARUS44*Server*
    5
    RARUS45FALSEChecklist*for*the*RARUS07*Server*
    6
    PARUS05FALSEChecklist*for*the*RARUS44*Server*
    7
    RARUS46TRUEChecklist*for*the*RARUS46*Server*
    8
    PARUS06FALSEChecklist*for*the*RARUS44*Server*
    9
    RARUS47FALSEChecklist*for*the*RARUS43*Server*
    10
    RARUS03TRUE**
    11
    RARUS07TRUE**
    12
    PARUS08FALSE**
    13
    RARUS44TRUE**
    Sheet4*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Perhaps the OP can shed some more light on what the real requirements are.

    Regards!

    Yogi Anand

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    Good point Yogi. That situation didn't even cross my mind. But you are probably right.

    Note: the following formula is the same thing: =COUNT(SEARCH(A1,$C$1:$C$9,1))>0
    You don't need the IF formula.

    Al

    [ This Message was edited by: Al Chara on 2002-07-28 16:32 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi Al:

    Thanks buddy ... for removing some redundant keystrokes.

    Regards!

    Yogi Anand

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com