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

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

"Dileep Chandran" wrote:

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

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

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

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

Thank You Buddy !!!

It works cool.

-Dileep