excel - INDEX MATCH not giving me the desired result -

i need little bit of help. trying use index match formula not giving me desired result. here query.

i have 2 tables. table 1 summary sheet , table 2 list of specific customers. in table table 1, there column of customer name , commission amount.

i want when there customer name entered in customer column , name listed in table 2, multiply column 3 10 otherwise state 0.

what have tried follows: if(match([customer name],table2[commission],0),[quantity]*0.1,"0")

it returns value when customer name entered customer column name listed in table 2 doesn't show 0 when other customer name entered name not listed in table 2. kindly review , me out.

thanks.

generally should not use if that. if evaluating statement determine whether true or false. in case match return #n/a (an error) when match not found, causes expression stop being evaluated because error returned.

there several ways deal this, start using iserror determine whether match found.

=if(iserror(match([customer name],table2[commission],0)),0,[quantity]*0.1) 

now function check whether match evaluated error. if error, return 0, otherwise return [quantity]*0.1.