#1   Report Post  
cjjoo
 
Posts: n/a
Default Match Index


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

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default Match Index

=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" wrote in
message ...

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



  #3   Report Post  
cjjoo
 
Posts: n/a
Default Match Index


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

  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default Match Index

=B2

is all you need as I read it

--

HTH

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


"cjjoo" wrote in
message ...

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match or Index Question carl Excel Worksheet Functions 2 October 4th 05 09:11 PM
Index and Match issues Mo Excel Worksheet Functions 3 May 19th 05 07:16 PM
Match & Index Phyllis B. Excel Worksheet Functions 2 November 27th 04 04:26 PM
Find a match that;s not exact Phyllis Excel Worksheet Functions 0 November 8th 04 09:12 PM
Vlookup, Index & Match Phyllis Excel Worksheet Functions 1 November 8th 04 07:11 PM


All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"