Selvaraj,

=IF(ISERROR(MATCH(PossiblyNotThereValue, CritValues, False),
INDEX(Values,MATCH(Fixed,CritValues,False)),
INDEX(Values,MATCH(PossiblyNotThereValue,CritValue s,False)))

like so, if your criteria are in A2:A12, and the information you want returned is in B2:B12:

=IF(ISERROR(MATCH("Selvaraj", A2:A12, FALSE)), INDEX(B2:B12,MATCH("Always There",A2:A12,FALSE)),
INDEX(B2:B12,MATCH("Selvaraj",A2:A12,FALSE)))

HTH,
Bernie
MS Excel MVP


"selvaraj" wrote in message
...

Hi,

I am having trouble referencing an array of cells within the IF
function, which need to be searched for a certain criteria.

I need the formula to search for a criteria, and if found return the
value next to the criteria.

If the criteria is not found then I want value next to another fixed
criteria (which I know will always be there) to be returned.

Any suggestions?

Thanks


--
selvaraj
------------------------------------------------------------------------
selvaraj's Profile: http://www.excelforum.com/member.php...o&userid=25674
View this thread: http://www.excelforum.com/showthread...hreadid=390928