# match a word in a range

1. ## 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

Formula:

Regards, TMS

3. ## 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:

 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

4. ## Re: match a word in a range

Try this in b20

Copied down

If it is not ok, please explain more so that you may get answer earlier

5. ## 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?

6. ## 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. ## 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. ## 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

9. ## 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:

10. ## Re: match a word in a range

thanks. thats it!!

11. ## Re: match a word in a range

Thank you for the feedback.

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

#### 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