Need help in formula - IF + Find + Vlookup

[Dileep Chandran]

Hi,

I was trying to create a formula. But its not working out.

This is what exactly I need to do. If the cell A1 contains "Test" B1
has to give "Test". If A1 does not contain "Test", then VLOOKUP the
first 19 characters in sheet 2 and give the corresponding value in B1.

The formula I have given is:

=IF(FIND("Test",A1,1),"Test",VLOOKUP(LEFT(A1,19),'sheet
2'!$A$1:$B$250,2,))

What may be wrong in this?

-Dileep

 

[Guest]

=IF(A1="Test","Test",VLOOKUP(LEFT(A1,19),'sheet
2'!$A$1:$B$250,2,))

 

[Dileep Chandran]

Thanks !!

But in this case, B1 will return "Test" only if A1 is equal to "Test".

I need B1 to give "Test", if A1 contains the word "Test".

Say for example, B1 should return "Test" even if A1 is "HelloTest" or
"Test123".

Any more ideas?

-Dileep

 

[Dileep Chandran]

Sorry. A small change in the formula. It should read:

=IF(SEARCH("Test",A1,1),"Test",VLOOKUP(LEFT(A1,19),'sheet2'!$A$1:$B$250,2,))

-Dileep

 

[jk.shivakumar]

Here is the formula:

IF(ISERROR(SEARCH("Test",A1,1)),VLOOKUP(LEFT(A1,19),Sheet2!$A$1:$B$250,2,),"Test")

Wanna More?

Throw some more complex situations!!!!!!!

 

[Dileep Chandran]

Thank You Buddy !!!

It works cool.

-Dileep