Isnumber & index match - can they be combined?

Hi,
I'm a bit stuck and hoping someone can help.

I'm want to use a few functions together, and I'm not sure if it's possible

What I want to do is start off by checking a cell contains specific values. I have
=IF(ISNUMBER(SEARCH("EJV OH",C4))

Then if the the field does contain the values I wanted to do an Index Match on a large table. Normally I would use something like :
INDEX('SHEET1'!LX:LX,MATCH(B4,'SHEET1'!A:A,0)
The data field will either be blank or have a date. If it has a date I want the Value "Rejected", if it doesn't have a date value, I want the formula to carry out another Index Match like:
INDEX('SHEET1'!FJ:FJ,MATCH(B4,'SHEET1'!A:A,0)
and that data field will also be blank or have a date, If it has a date I want the Value "Approved"

Any help would be greatly appreciated

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Please note rules on thread titles - I have updated yours for you today.

Untested, but try:

Formula:

Please Login or Register  to view this content.