Thanks Mike, amazing video. Just testing, i found what I think may be another way, but by using Index & Match with Sequence & Column functions. For row record => =INDEX(dEmp;MATCH(B40;dEmp[ID];0);1+SEQUENCE(;COLUMNS(dEmp[#Headers])-1)) For Column record => =INDEX(dEmp;MATCH(C42;dEmp[ID];0);1+SEQUENCE(COLUMNS(dEmp[#Headers])-1))
@@excelisfun Mike, I have Microsoft Office 365 on my PC, but I can not find this function in it. Can you please tell me what the reason can be? Thanks in advance and best regards. Salim
@@sasavienne Right now, as of Jan 2019, you have to sign up for the "Insider Edition" of Office 365 (google it), but MS says that all versions of Office 365 will have it soon.
Really amazing Mike, showing these variations will make dynamic array will make more dynamic. Will it be possible for do less than or greater than Match Look ups..
Hi Mike here is the lookup table Look up Table Item Amount tt1 10 tt1 20 tt1 20 tt1 40 tt1 110 tt1 1000 tt1 44 tt2 110 tt2 140 tt2 444 tt2 555 tt2 44 Below the list of the value more than amount Item Amount Output- All Amount more than >30 tt1 30 40 44 110 1000 Please advise how to achieve it.
Micrsosoft says Office 2019 does not have Dynamic Arrays. If you can avoid it, do NOT buy Office 2019. It will not get any of the new features that Microsoft will be releasing in the years to come...
Return an entire record minus the first field. At last, a use case where the nations favourite, VLOOKUP, has the edge over INDEX/MATCH! :-) Utterly trivial but the SEQUENCE could be simplified by recognising that the Headers and the Table Body have the same number of columns. I suspect the simplest solution would be to use FILTER on the entire table and not try to embed the query within the returned record/s.
Yes, I think I have a video that shows something close: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-y64H6NntpLk.html&list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx&index=17 But I do not think it would be too hard. In Fact I think we can just use FILTER. Try this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5zRab2Grz7Q.html&list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx&index=6
If one were intent on using function FILTER while still accounting for expanding columns to the right of the Excel Table, this construction would do it. In C31 =FILTER(FILTER(dEmpANSWER,dEmpANSWER[ID]=B31),FILTER(dEmpANSWER,dEmpANSWER[ID]=B31)B31)
@@SanviPatil2019 ok, then the link I sent above is just what you need! It's an Excel Is Fun video and it explains how to create drop down lists. Enjoy!
In every video in which is used the new Excel Calc Engine should be Always mentioned that to use it you must bound your bank account to Microsoft for the rest of you life… if it happens that for any accident you can't pay the monthly fee, those features are not reversible and they will not work on a normal version of Excel. Say bye bye to the 100 hours spent building a solution for you or your client. I'm the only one out there who thinks that this microsoft marketing policy it is a complete nonsense ?
It maybe complete nonsense, but the future of all applications from all companies is subscription based in the cloud. And that is what Office 365 is. In a year or two, we will not be able to avoid it. The disatvantage is that we have to pay each month, the advantage is that the version will always be updated, and eventually everyone will always have the correct version.
@@ExcelStrategy , I do not think we have to attach to Microsoft for eternity, but for now, the Excel and Power BI Data Analysis tools are pretty amazing and useful : )
Yes, I think I have a video that shows something close: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-y64H6NntpLk.html&list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx&index=17 But I do not think it would be too hard. In Fact I think we can just use FILTER. Try this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5zRab2Grz7Q.html&list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx&index=6
