Excel Magic Trick 758.5: Extract Data With Four Criteria VLOOKUP & INDEX SUM, AND, OR Functions

Download Excel Start File people.highlin...
Download file: people.highlin...
See two different non-array formula methods for extracting data with four criteria based on criteria in cells and formulas. See the SUM, AND, OR, IF, MAX, VLOOKUP, INDEX, MATCH functions.
This video is in response to:
Excel Magic Trick 758: Extract Data With Two Criteria - 5 Formula Methods
Extract Records (data) With Multiple Criteria Helper Column

[@KevinPGA]

WOW, 12 years later, Microsoft 365 has made this so much easier, but there are still many concepts in these old videos that are valid and super helpful today!

[@excelisfun]

Yes indeed - that is a good idea.
I think in most cases, I think that this method would be faster calculating (good for large spreadsheets) because there are fewer ranges and cells to calculate upon.

[@krn14242]

Cool, extract without array formula. Useful, thanks Mike.

[@excelisfun]

Formulas update instantly when criteria in cells (formula inputs) change, and for some applications this is desirable. Filter, PivotTables and the like are MUCH easier and should be used in most cases.

[@excelisfun]

@lightsaberification , I do not have a video on that topic. Good one I may do soon. In the mean time post your question to this alternative site (not affiliated with excelisfun at RUclips):
mrexcel [dot] com/forum

[@excelisfun]

Ya, as long as you don't mind an extra column, I kind of like this method over the SMALL / ROW etc. methods...

[@cookiemaster9729]

Hello, Mr. ExcellsFun. You are so cool!!!

[@excelisfun]

Glad the video helped!

[@MohammadTaha]

Filtering can solve this very fast, simply let it show only the rows where there is a true value, but it is nice to do it using formulas :)

[@excelisfun]

Filtering is much easier. I do Filter ALL the time. If you need it automated, then a formula solution may work...

[@yippee2288]

Your videos are very useful and informative! Actually I am just a beginner in Excel. So I use the Filter function to extract data using the above example. First, I filter column D by selecting "Type 1" and "Type 2". Second, I use the date filter in column G to set the date between 1/8/2009 to 30/9/2009. The result is the same as your illustration. What is the difference between these two methods? Thanks.

[@lightsaberification]

is it possible to use the above mentioned method to extract data from a list of workbooks with the workbook name being one of the criteria?? if not what would you recommend?

[@subaktagin]

Thanks

[@joelvantine9177]

I am trying to do something similar to what you demonstrated in Trick 758.5, but as a relative newbie, I am having great difficulty. I have a worksheet with dates in column b, descriptions in column c, and amounts in column d. On another sheet, I am trying to search within a set range of dates on the first sheet and within that date range, find a partial string match in the description column and display the amount for each match. Can you help?

[@krn14242]

@ExcelIsFun Me too, I guess you can always just hide the column or place on another tab and then hide the sheet. Call it the EXTRACT formula sheet lol.

[@deepakmirchandani1348]

great and useful video sir. further can we extract values by vlookup from a cell or an array, having already a vlookup formula?

[@excelisfun]

I am sorry, I do not know how to do that. You can try posting your question here: mrexcel.com/forum

[@deepakmirchandani1348]

@@excelisfun thanks sir. pleasure to hear you.