1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

EXCEL Searching Data in Rows/Columns

Discussion in 'Business Applications' started by aernst, Jul 5, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. aernst

    aernst Thread Starter

    Joined:
    Jul 5, 2004
    Messages:
    9
    Hi

    (A)
    does anyone know how to search for the text content of a cell through a column and then return the value/content of the cell of a certain other column of the same row where the text content was found?
    e.g.

    A1 B1 C1 D1
    2 013004T XY
    3 092004TP YZ
    4 012004TP ZY
    5 052004P YX
    6 092004TP YY [FORMULA]
    7 072004TP ZZ


    >>FORMULA: "Look for string '092004TP' in column A (other than in row 6) and return the value of that row in column C (C3=YZ)."
    NOTE: There might be more than 1 row with the string '092004TP' in column A - I would need the sum of all results in column C where the FORMULA finds this string.

    (B)
    I also have a similar problem with another worksheet:
    It would require a formula which searches through a whole column for certain names, e.g. "Donna", and then returns the value of another column in the same row where it found "Donna"; the value, again, is not in a column directly adjacent to the column where "Donna" would be found.
    This is fairly identical to problem (A), except that the search string would be unique (only 1 result), and it doesn't have to take into account (and disregard) if the string was found in the same row of the FORMULA (the search column would be in a different worksheet from the FORMULA worksheet).

    Thanks for any suggestions!

    Cheers
    from New Zealand

    Andreas
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Welcome to the board Andreas.

    I don't entirely follow your question. But if I assume that the key part is "I would need the sum of all results in column C" & that "XZ", "YZ", "ZY" etc. are actual numbers, then

    =SUMIF(B1:B100,"092004TP",C1:C100)

    HTH,
    Andy
     
  3. aernst

    aernst Thread Starter

    Joined:
    Jul 5, 2004
    Messages:
    9
    Many thanks, Andy, that worked perfect!
    Best
    A.
     
  4. aernst

    aernst Thread Starter

    Joined:
    Jul 5, 2004
    Messages:
    9
    Getting the sum out of a certain column worked fine; how does it work if I want to retrieve text out of a certain column, in the same row where my search string is found?

    e.g.
    The FORMULA should look for the name "Donna" in column A where all first names are listed, and then retrieve her address (string of text) out of column D where all addresses are listed; her address is in the same row as her name.

    Table1
    A - B - C - D
    Name Tel.Nr. Email Address
    ...
    ...
    Donna ... ... 32 High St.

    Table2
    ...
    ...
    FORMULA: "Search for 'Donna' in Table1!A:A and show her address (=32 High St.)."


    Cheers,
    Andreas
     
  5. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Hi again. The formula ;

    =VLOOKUP(g1,a2:d100,4,FALSE)

    g1 = the cell containing what to search for ("Donna").
    a2:d100 = the location of the Name/#/email/address table.
    4 = which column in the table to get the result from, i.e address.

    Rgds,
    Andy
     
  6. aernst

    aernst Thread Starter

    Joined:
    Jul 5, 2004
    Messages:
    9
    Thanks again, that's much appreciated.
    I searched for quite some time in the Excel help, but your solution just took a minute.

    Best regards,
    Andreas
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/246570

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice