Hi Alan. Great legacy examples! New EXCEL sure simplifies the formula/function gymnastics previously required to solve these challenges. Thanks for sharing :)) Thumbs up!!
Hi, may I know how to make it can match with two criteria, such as the category:beverage and price:2.8. Then it will return all the products that match the 2 criteria. Thanks in advance.
@@Computergaga This amazing array study is so useful for finding multiple matches and basis of XLookup function in Excel. Nobody knows this secreet and you made a very useful contribution for advanced users. Thanks a lot
Your formula looks good, but I made the selection by clicking the table column header. Be careful to not select the sheet column header. I clicked the header in the table.
dont know why people even bother learning this shit using microsoft products, its just as easy to learn actual coding using language such as php or Java.
The solution for the first exercise seems to me to be better using the following formula: =INDEX(products[Product],SMALL(IF(products[[ Category]]=F3,ROW(products[[ Category]])-ROW(products[[#Headers],[ Category]]),""),ROW(INDIRECT("1:"&SUM(N(products[[ Category]]=F3)))))) 🤗
See how much easier this is in Excel 365 - th-cam.com/video/5XB7C_TF5LU/w-d-xo.html
This video helped me a lot! Combining all the results in a single cell is just what I needed. Thanks :)
You're very welcome. Thank you!
Hi Alan. Great legacy examples! New EXCEL sure simplifies the formula/function gymnastics previously required to solve these challenges. Thanks for sharing :)) Thumbs up!!
Thanks, Wayne. New Excel rocks 💪👊
Thank You. That Cntrl Shift Enter trick (in 2019 version) helped me to get results.
Excellent! 👍
Great examples . . . SMALL comes to the rescue. Cheers.
Thank you, Chris.
Appreciated and I learn something new .thanks for ur time sharing ur knowledge to us.
Great! Thank you 👍
Superb Sir 👍👍😁😃
Thanks a lot 😊
Hi, may I know how to make it can match with two criteria, such as the category:beverage and price:2.8. Then it will return all the products that match the 2 criteria. Thanks in advance.
What if I want to use the returned values ("Coffee" and "Tea") and count how many times those values are seen in a different table?
Can we use this formula as data validation formula for a cell
If you're thinking of a drop-down list Sunil, you would reference the range of cells for the list and not the formula directly.
Good tutorial thank you. Can you share example file?
Thank you, Emre. File is available in the description 👍
@@Computergaga This amazing array study is so useful for finding multiple matches and basis of XLookup function in Excel. Nobody knows this secreet and you made a very useful contribution for advanced users.
Thanks a lot
How can I remove duplicates from the results?
Good job! Computergaga..
Thank you, Usman.
How did you do the selection in 12:22? My formula is having error by just typing if(products[Category]=F3.
Your formula looks good, but I made the selection by clicking the table column header. Be careful to not select the sheet column header. I clicked the header in the table.
Dear Sir,
Can you plz explain how it has been done naming like this ...products[Product] and (products[Category] ...???
Sure. They are the structured references of a table. You can convert a range to a table by clicking a cell in the range and clicking Insert > Table.
@@Computergaga
Thank you sir
I understood...😍
Dear Sir , if u know any formula to winning a lottery. Need to choose 6 numbers from 01 to 49. So plz tell me Thanks .
That's just luck, Muhammad.
👍👍👍
dont know why people even bother learning this shit using microsoft products, its just as easy to learn actual coding using language such as php or Java.
It confuses me why you are watching it then.
The solution for the first exercise seems to me to be better using the following formula:
=INDEX(products[Product],SMALL(IF(products[[ Category]]=F3,ROW(products[[ Category]])-ROW(products[[#Headers],[ Category]]),""),ROW(INDIRECT("1:"&SUM(N(products[[ Category]]=F3)))))) 🤗
Thanks for the formula, José.