PDA

View Full Version : dual search criteria over two ranges (ie column)



amrane
09-20-2014, 10:23 AM
Dear Forum
I am looking for some VBA code to return the rows including both criteria over two ranges,

using excel formul, I am using the array formulas, its doiing good job, so but how this search over vba funtion retrung back the row index??

I found some pages (http://www.cpearson.com/excel/findall.aspx), but I didn't get the idea!!

thanks in advance for your support,
br,a amrane

p45cal
09-20-2014, 01:53 PM
Your formulae don't appear to work on the sheet you supplied.
However, if you place this formula in H1 (or any column but it must be in row 1 of the sheet):
=SMALL(IF(($C$5:$C$19=$C$1)*($D$5:$D$19=$D$1),$B$5:$B$19),ROW())
and array-enter it with Ctrl+Shift+Enter, then copy down until you get the #NUM! error, you will get your indices.

Do you still need vba?

If you copy down to more than the max number of rows you expect to find, you could wrap IFERROR arunf the formula:
=IFERROR(SMALL(IF(($C$5:$C$19=$C$1)*($D$5:$D$19=$D$1),$B$5:$B$19),ROW()),"")