# 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  Register To Reply

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

Formula:  `Please Login or Register  to view this content.`

Regards, TMS  Register To Reply

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:  `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  Register To Reply

4. ## 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  Register To Reply

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?  Register To Reply

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)  Register To Reply

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.  Register To Reply

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  Register To Reply

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:  `Please Login or Register  to view this content.`  Register To Reply

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

thanks. thats it!!  Register To Reply

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

Thank you for the feedback.  Register To Reply