Lookup Mid$

[racer25]

Hi Guys

I am trying to do some kind of lookup formula, I have the followin
data

Example 1

A101 John Smith
A102 This player is on a complaint list
A103 Rank: 3 Star General

Example 2

A101 Paul Brown
A102 Rank: Captain

I am trying to do a lookup formula that will look at cells A101 to A10
find the word "Rank:" and return the text innediately to thr right of i
i.e. 3 Star General or Captain etc.

The information for Rank can be on either A102 or A103, this data i
sourced from a web query.

Any guidance would be most appreciated.

Thanks

Race

 

[Frank Kabel]

Hi
try:
=TRIM(MID(LOOKUP(2,1/SEARCH("Rank:",A101:A103),A101:A103),SEARCH("Rank:
",LOOKUP(2,1/SEARCH("Rank:",A101:A103),A101:A103))+5,255))

This is not case sensitive. If you require this, change SEARCH to fIND.

 

[racer25]

Thanks, thats absolutely spot on