Vlookup a specific word

[nagesh.tvsr]

Hi,

Need to find a specific word via vlookup.

An excel file attached with necessary details.

Pl. help.

Thanks,
Nagesh.

[Pete_UK]

You can use this formula in C2:

=IF(COUNTIF(I:I,B2),IF(ISNUMBER(SEARCH("sip rksp",VLOOKUP(B2,I:J,2,0))),"Yet to clear",IF(ISNUMBER(SEARCH("sip",VLOOKUP(B2,I:J,2,0))),"Cleared","Yet to clear")),"")

then copy down.

Hope this helps.

Pete

[nagesh.tvsr]

Hi,

This is working well. But part of the formula (given below) to be changed. RKSP will not always come next to SIP. Formula to take care of this. Existing formula is not taking care of it.

IF(ISNUMBER(SEARCH("sip rksp",VLOOKUP(B2,I:J,2,0))),"Yet to clear",

Pl. do the needful.

Thanks,
Nagesh.

[bebo021999]

c2:

Please Login or Register  to view this content.

[nagesh.tvsr]

Thank you Bebo. Its working perfectly.

Thank you Pete for the help extended.

[Bo_Ry]

Is this work?

=IF(J2="","",IF(SUMPRODUCT({1,0}-ISERR(SEARCH({"RKSP","SIP"},J2))),"Yet to clear","Cleared"))