4 ms·
I almost cried when I saw the word return_array. Then I realized my mistake. Return_array is the name of an input parameter. How I long for the day I can retur
by nashashmi 7y ago
I almost cried when I saw the word return_array. Then I realized my mistake. Return_array is the name of an input parameter.
How I long for the day I can return multiple values from an excel lookup function. Few users know of such a thing. But it is amazing. So far I have gotten it to work using the most complex formula string I ever made.
Link: https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#vertically https://www.get-digital-help.com/2009/10/25/how-to-return-mu...
- cm2187 7y agoOr do a lookup over multiple columns without having to do some ugly concatanations
- macky360 7y agoTime to start crying. You can return an array. Just provide a 2D array to the return_array parameter.
- nashashmi 7y agoFrom what I understand about xlookup, it will only return back the first value and not multiple values.
- parsimo2010 7y agoExcel has allowed array formulas for literally decades. It’s not as complicated as the link you posted makes it seem. You just need: 1. A formula that supports array returns (like MMULT) 2. Know how big the returned array dimensions are 3. Highlight that array size, type in the formula, and press ctrl+shift+enter Returning multiple matches from a single criteria isn’t what VLOOKUP was designed for, so rather than complain that a hammer does a bad job at screwing in nails, search for a screwdriver. Excel has tons of data filtering and table features, and allows DB connections for SQL queries.
- nashashmi 7y agoNever knew of mmult. Will try to look at that technique to simplify the formula. Aside from that, another user posted excel campus example of filter function. That also returns an array. And pretty much does what I need. However, it is only for office 365 insider and not to be released anytime soon.
- longemen3000 7y agoFot what I know, Microsoft is working on an improvement of arrays in excel: https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/ https://www.excelcampus.com/functions/dynamic-array-formulas...
- nashashmi 7y agoThanks for the link. I see filter function as helping me do the same thing as the link I posted.