More than one "IF" formula in a string

[merryperson]

Hi I am in the UK and analysing data for VAT purposes and want to produce a report with all transactions which show the VAT % of more than 21% less than 19% but not 0%.

The data I will have is as below
Column A (name) Column B (VAT %)
Dave 21%
Bill 15%
Brian 20%
Steve 20%
Jane 0%

I was going to use an IF formula to pick the results where the VAT % in column B was > 21% and <than 19% but this would then bring all the 0% as well which I don't need in the report.
The report would pull out the names Dave and Bill but not show Jane

Is there a way I can say greater than 21% less than 19% but not 0% in a formula
Any ideas?

 

[Marcelo Branco]

Try this array formula in D2 copied down
=IFERROR(INDEX(A$2:A$6,SMALL(IF((B$2:B$6>=0.21)+(B$2:B$6>0)*(B$2:B$6<0.19),ROW(A$2:A$6)-ROW(A$2)+1),ROWS(D$2:D2))),"")
Ctrl+Shift+Enter

M.

 

[merryperson]

Hi Sorry and unless I am doing something wrong it picks up the 21% but not the 15% but what is the Ctrl+Shift+enter for?

 

[Marcelo Branco]

Hi Sorry and unless I am doing something wrong it picks up the 21% but not the 15% but what is the Ctrl+Shift+enter for?

It's an array formula so it must be confirmed with Ctrl+Shift+Enter simultaneously, not just Enter
If you're not familiar with array formulas take a look at
http://www.cpearson.com/excel/ArrayFormulas.aspx

M.

 

[merryperson]

well thats something I have learnt today and thanks for all your help much appreciated and works a treat

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.