Match Index

[cjjoo]

just wanna ask about the use of match and index. in this scenario, i
want the cell to perform a match index and if there is a match, it will
return the value, if thee is no match, carry out another formula. This
is the scenario:


A B
01 x123
02 y456
01 x123

in this e.g, the formula use in A2
=INDEX(A$2:A2,MATCH(B2,B$1:b2,FALSE))

but if there is no match, the cell will return a NA but now i would
like the cell to perform another formula if there is no match . Can it
be done using IF function?

something like if match, do_match(index),
if_no_match_carryout_other_formula?

hope that i am clear..


pls advise

 

[Bob Phillips]

=IF(ISNUMBER(MATCH(B2,B$1:b2,FALSE)),INDEX(A$2:A2,MATCH(B2,B$1:b2,FALSE)),ot
her_formula)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[cjjoo]

sorry to ask u something again...

in the previous eg, in b2 , it is key in by the end user.So now, i want
(in A2) that

reads if there is any input in B2. IF b2="", return "", If there is an
input, match

with previous records , if_match, return_match_value, if_no_match,
carry out

another formula.

i am not sure how to arrange all this in the nested IF s format in the
logical

sequence , pls advise.....

 

[Bob Phillips]

=B2

is all you need as I read it

--

HTH

RP
(remove nothere from the email address if mailing direct)