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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> cjjoo
> ------------------------------------------------------------------------
> cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
> View this thread: http://www.excelforum.com/showthread...hreadid=478698
>

[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.

the values in column A are in date formats.

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)


"cjjoo" <[email protected]> wrote in
message news:[email protected]...
>
> 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.....
>
>
> --
> cjjoo
> ------------------------------------------------------------------------
> cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
> View this thread: http://www.excelforum.com/showthread...hreadid=478698
>