Searching for a criteria in array of cells within an IF statement

[selvaraj]

Hi,

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

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

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

Any suggestions?

Thank

 

[Bernie Deitrick]

Selvaraj,

=IF(ISERROR(MATCH(PossiblyNotThereValue, CritValues, False),
INDEX(Values,MATCH(Fixed,CritValues,False)),
INDEX(Values,MATCH(PossiblyNotThereValue,CritValues,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