+ Reply to Thread
Results 1 to 11 of 11

match a word in a range

  1. #1
    Registered User
    Join Date
    02-14-2015
    Location
    ireland
    MS-Off Ver
    win 7
    Posts
    11

    match a word in a range

    hi. i have attached my sheet. if a word in the range a20:a100 is in cell a5, then i want a formula that will return 100 in cell b20. if the word is in cell a8, then i want 103 returned in b23.
    Hope you guys can sort me out for this one!!
    thanks
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,605

    Re: match a word in a range

    I don't follow your logic but this might get you started:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: match a word in a range

    If I understand you correctly, if the value in A20 matches the value in A5 then return 100 and if the match is somewhere else in A5:A15 return 103. If no match, return nothing.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A
    B
    5
    Call Me Vic
    6
    the who
    7
    Banditry
    8
    Greenlaw
    9
    Red Unico
    10
    Poolstock
    11
    Edge
    12
    Red Inca
    13
    Rough Courte
    14
    Centre Haafhd
    15
    Lady Sugarfoot
    16
    17
    18
    19
    20
    Call Me Vic
    100
    21
    Greenlaw
    103
    22
    Still Believing
    23
    Belmount
    24
    Howaboutnow
    25
    Red Anchor
    26
    Storm Of Swords
    27
    My Brother Sylvest
    28
    Easily Pleased
    29
    Lily Little Legs
    30
    Speedy Bruere
    31
    Venetian Lad
    32
    Ilewin Geez
    33
    Welluptoscratch
    34
    Renfrew
    Last edited by newdoverman; 05-10-2015 at 10:39 AM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    726

    Re: match a word in a range

    Try this in b20

    Please Login or Register  to view this content.
    Copied down

    If it is not ok, please explain more so that you may get answer earlier
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,315

    Re: match a word in a range

    Aengus, you've got us a bit confused, I think... Certainly I am. Can you explain the logic that lies behind what you want?
    Glenn



  6. #6
    Registered User
    Join Date
    02-14-2015
    Location
    ireland
    MS-Off Ver
    win 7
    Posts
    11

    Re: match a word in a range

    thtas not what im looking for guys. here is the formula i have at the moment and it works perfect. but its so long and i need to repeat in the next cell down for about 50 cells. so im looking for an easier way of writing this formula
    =IF(OR(A$62=A5,A$63=A5,A$64=A5,A$65=A5,A$66=A5,A$67=A5,A$68=A5,A$69=A5,A$70=A5,A$71=A5,A$72=A5,A$73=A5,A$74=A5,A$75=A5,A$76=A5,A$77=A5,A$78=A5,A$79=A5,A$80=A5,A$81=A5,A$82=A5,A$83=A5,A$84=A5,A$85=A5,A$86=A5,A$87=A5,A$88=A5,A$89=A5,A$90=A5,A$91=A5,A$92=A5,A$93=A5,A$94=A5,A$95=A5,A$96=A5,A$97=A5,A$98=A5,A$99=A5,A$100=A5,A$101=A5,A$102=A5,A$103=A5,A$104=A5,A$105=A5,A$106=A5,A$107=A5,A$108=A5,A$109=A5,A$110=A5,A$111=A5,A$112=A5,A$113=A5,A$114=A5,A$115=A5),100,0)
    so the next cell will be,
    =IF(OR(A$62=A6,A$63=A6,A$64=A6,A$65=A6,A$66=A6,A$67=A6,A$68=A6,A$69=A6,A$70=A6,A$71=A6,A$72=A6,A$73=A6,A$74=A6,A$75=A6,A$76=A6,A$77=A6,A$78=A6,A$79=A6,A$80=A6,A$81=A6,A$82=A6,A$83=A6,A$84=A6,A$85=A6,A$86=A6,A$87=A6,A$88=A6,A$89=A6,A$90=A6,A$91=A6,A$92=A6,A$93=A6,A$94=A6,A$95=A6,A$96=A6,A$97=A6,A$98=A6,A$99=A6,A$100=A6,A$101=A6,A$102=A6,A$103=A6,A$104=A6,A$105=A6,A$106=A6,A$107=A6,A$108=A6,A$109=A6,A$110=A6,A$111=A6,A$112=A6,A$113=A6,A$114=A6,A$115=A6),101,0)

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,315

    Re: match a word in a range

    Phuq me!!! Can you try to explain again what it's doing. It might help if we could see your real sheet to see why you want something that, on first view, looks very odd indeed.

  8. #8
    Registered User
    Join Date
    02-14-2015
    Location
    ireland
    MS-Off Ver
    win 7
    Posts
    11

    Re: match a word in a range

    haha. its a betting sheet. so i get a list of horses in the morning and paste them into A62. the values in a5:A40 update automatically for every race.
    so when a name in A62:a115
    Attached Files Attached Files

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: match a word in a range

    This is even more confusing than before. The first data set is too small and too few matches to make sense of this.

    Please post your actual workbook because just having the two formulae doesn't make a whole lot of sense the way it is. We don't know where or what you are doing with these formulae. Are you filling them down, are you re-writing formulae for each row or what?

    In the mean time, try this in B5 and fill down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by newdoverman; 05-10-2015 at 02:49 PM.

  10. #10
    Registered User
    Join Date
    02-14-2015
    Location
    ireland
    MS-Off Ver
    win 7
    Posts
    11

    Re: match a word in a range

    thanks. thats it!!

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: match a word in a range

    Thank you for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Look for a word with a range and only display a row where the word is listed
    By bbarth in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-15-2015, 10:29 AM
  2. Replies: 3
    Last Post: 01-22-2015, 06:21 PM
  3. [SOLVED] If match word Needs and value is < 5 return the word Comb
    By score in forum Excel General
    Replies: 3
    Last Post: 12-18-2014, 06:54 PM
  4. [SOLVED] Dynamic Name Range using Offset&Match with Match based off a different column
    By mdlpjr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2013, 06:33 PM
  5. Match one word with the same word in a large table
    By Warwickk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-24-2010, 10:07 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1