Match function not coming correct.

[SONJOE JOSEPH]

Dear Excel Experts,

I was just working on a file were the option moneyness are given in 'A Column' (ie whether the options are ITM, ATM & OTM) along with the Symbol List and Strike Value. I''m just trying to find the exact match of the CE ITM and PE ATM from the strike value list. I have given the formula which is in 'H Column' but the Ans is coming 2 & 3 which is wrong. The correct Ans has to be 8000 and 8100 which i have shown in 'column I'.

Do help me out in solving the issue and the working file is attached along with this message.

Regards,

Sonjoe Joseph.

 

[Hui]

Sonjoe

what is wrong with?

=INDEX(C2:C11,MATCH("CE ITM",A2:A11,0))
=8000
and
=INDEX(C2:C11,MATCH("PE ATM",A2:A11,0))
=8100

 

[SONJOE JOSEPH]

Sonjoe

what is wrong with?

=INDEX(C2:C11,MATCH("CE ITM",A2:A11,0))
=8000
and
=INDEX(C2:C11,MATCH("PE ATM",A2:A11,0))
=8100

Thank You Hai....its working perfectly fine. Great.

 

[bosco_yip]

Hi,

1] Match function only return the matching row number and does not return the value.

2] Here's the proposed Output Table set up in G7:I9 for your information.

3] The "Strike Value" I8, formula copy down :

=VLOOKUP(H8,A$2:C$11,3,0)

4] Or, you can use INDEX+MATCH function instead of VLOOKUP function.

In J8, formula copy down :

=INDEX(C$2:C$11,MATCH(H8,A$2:A$11,0))

p.s. The difference between VLOOKUP and INDEX+MATCH function :
Generally VLOOKUP will faster than INDEX+MATCH by approx. 5%, but in some occasions INDEX+MATCH will faster than VLOOKUP, depending in how do you used them.

Regards
Bosco

 

[SONJOE JOSEPH]

Hi,

1] Match function only return the matching row number and does not return the value.

2] Here's the proposed Output Table set up in G7:I9 for your information.

3] The "Strike Value" I8, formula copy down :

=VLOOKUP(H8,A$2:C$11,3,0)

4] Or, you can use INDEX+MATCH function instead of VLOOKUP function.

In J8, formula copy down :

=INDEX(C$2:C$11,MATCH(H8,A$2:A$11,0))

p.s. The difference between VLOOKUP and INDEX+MATCH function :
Generally VLOOKUP will faster than INDEX+MATCH by approx. 5%, but in some occasions INDEX+MATCH will faster than VLOOKUP, depending in how do you used them.

Regards
Bosco

Thank you bosco for your comment and it was a great piece of learning information.

Regards,

Sonjoe Joseph.