Return all rows matching multiple criteria

[agutt]

Hey all. Don't wish to filter. I guess Index/Match, but man that gets complicated fast, and I couldn't find examples that used both strings and values as I need here.

As an example, I set up this table (sorry, couldn't get xl2bb to work):

Off to the right, I just manually pasted over the correct values for my selection (contains ham and mayo is <$9), but of course I'm seeking a formula to automate this. Multiple criteria, some text some integer.

TIA

 

[Fluff]

Hi & welcome to MrExcel.

sorry, couldn't get xl2bb to work

In what way? Are the copy icons greyed out, if so have a look here XL2BB Icons greyed out

 

[agutt]

Hi & welcome to MrExcel.

In what way? Are the copy icons greyed out, if so have a look here XL2BB Icons greyed out

Security warning, macros disabled, tried to manually unblock the zip file, seemingly no diff.

 

[Fluff]

You need to extract the add-in from the file & copy that to the add-in folder.

 

[agutt]

Okay, got it there now but unsure how to edit my original post.

 

[Joe4]

Since you are using 365, why not use the new FILTER function (which is different from Filters/Advanced Filters)?
It is very easy using this (see: FILTER Function)

So here is what your example results would look like:

And all you have to do is out the following formula in cell I4, and everything spills over to the other columns and rows:

=FILTER(A2:E11,(A2:A11="Ham")*(D2:D11="Mayo")*(E2:E11<9))

You can even replace the hard-coded references of "Ham", "Mayo", and 9 with cell references, if you like.

 

[agutt]

Interesting, I didn't know this function existed. I have run into a problem however... the example I drew up here is just an example. My actual data/range is larger. Just tried your formula, inserting the correct fields (table range, (condition)*(condition)*(condition)) and I'm getting SPILL! error... not one I'm used to encountering. Any ideas?

 

[Joe4]

With these new functions that automatically "spill" over into other columns and rows in order to return all the matching data, you need to be sure to leave enough room!

So, if you are placing the formula in cell I4, and it is returning 10 rows of data, if you have something in cell I9, you are going to get an error because you did not leave enough room for it to return 10 rows! My guess if that you have not left enough room.

 

[agutt]

Thank you so much! Marking as solved...

 

[Joe4]

You are welcome. Glad I was able to help!
I love that new FILTER function myself! It is pretty cool...