Excel Code Query

Cotta

Hi All,

I have the following code in Excel, it basically look for a code and returns a separte value in an accompanying spreadsheet. However in front of this doe I want to include something to reflect that if cell A1 contains the word "Liverpool" then I want the word "Liverpool" returned instead of the below function being carried out. How do I do this?

=VLOOKUP(B2,'Q\Folder\Football\[Codes.xlsx]Sheet1'!$A$1:$G$670,7,FALSE)

Thanks

rmg1

Changing your formula to the following will probably do it:-

=if(b2="Liverpool","Liverpool",VLOOKUP(B2,'Q\Folder\Football\[Codes.xlsx]Sheet1'!$A$1:$G$670,7,FALSE))

Cotta

Perfect - thanks.

Mirno

What do you mean by "contains" - if you mean that it exactly matches then rmg1's code is perfect. If you mean that it should also match "Liverpool FC", or "I hear Liverpool are rubbish" then:

IF(ISERR(FIND("Liverpool", B2)), LOOKUP..., "Liverpool")

Will find the substring "Liverpool" anywhere within B2.

Mirno

EchoLocation

Mirno wrote: »

What do you mean by "contains" - if you mean that it exactly matches then rmg1's code is perfect. If you mean that it should also match "Liverpool FC", or "I hear Liverpool are rubbish" then:

IF(ISERR(FIND("Liverpool", B2)), LOOKUP..., "Liverpool")

Will find the substring "Liverpool" anywhere within B2.

Mirno

Could also use SEARCH, as FIND is case sensitive.

Cotta

Mirno wrote: »

What do you mean by "contains" - if you mean that it exactly matches then rmg1's code is perfect. If you mean that it should also match "Liverpool FC", or "I hear Liverpool are rubbish" then:

IF(ISERR(FIND("Liverpool", B2)), LOOKUP..., "Liverpool")

Will find the substring "Liverpool" anywhere within B2.

Mirno

Hey, less of the Liverpool are rubbish

Thanks to all for your feedback.