Lookup and return data based on multiple criteria

brchiz

New Member
Hello, I have been able to sucessfully adapt the below formula into my spreadsheet, however, I would like to know if you can add any criteria to the index information that is returned.

To be more specific, I have a worksheet with several columns of data that I have this formula returning information from. There is one column on that spreadsheet with values of 0 and 1. I want this formula to only return values from rows with a 1 in column AB.

Here is my current formula:
{=IF(ROWS(F\$6:F6)<=\$J\$4,INDEX('Oct 2011'!A:A,SMALL(IF('Oct 2011'!C:C=\$B\$4,ROW('Oct 2011'!C:C)-ROW(\$L\$6)+1),ROWS(F\$6:F6))),"")}

Here i what I have in J4:
{=SUM(('Oct 2011'!C:C='3 Month Drop'!B4)*('Oct 2011'!AB:AB=1))}

I want the formula in F6 to only return data from 'Oct 2011' if AB=1

Thanks
<!-- / message -->

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Replies
9
Views
146
Replies
1
Views
379
Replies
3
Views
340
Replies
7
Views
119
Replies
7
Views
155

1,202,987
Messages
6,052,930
Members
444,616
Latest member
novit19089

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back