Hi, I need help on getting the proper and simpler formulas on how to extract data and show it based on a selection in a dropdown. Please refer to the sample excel file below. If i choose Entrepreneurship Level 2 in the dropdown it should be able to list down every behavior that is level 2 from the table in sheet 2. Please help.
Put in E3 on Sheet1 and copied down until blank results
=IFERROR(INDEX(Sheet2!$A$2:$A$17,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$17)-MIN(ROW(Sheet2!$A$2:$A$17))+1)/(Sheet2!$B$2:$B$17=--RIGHT($E$1,1)),ROW(A1))),"")
Thank you for your input! Appreciate it!
Hi,
This involves a complicated formula that is essentially INDEX MATCH multiple returns.
See this website for explanation:
https://fiveminutelessons.com/learn-...le-values-list
See attached file for a working example with your data.
Hope this is helpful.
Cheers
See the attached file.
I used conditional formatting to update a border on your table as the formulas fill in the data, feel free to hide the helper column and your list if needed, was more or less an example. I removed the merged cell, simply because I don't like merged cells, feel free to re-merge it.
the main formula is:
and the helper column is:
=IFERROR(INDEX(Sheet2!$A$2:$A$17,MATCH($E$1&"_"&ROWS($1:1),Sheet2!$C$2:$C$17,0)),"")
I also used this formula to add borders to your table of results as they are filled in:="Entrepreneurship Level "&B2&"_"&COUNTIF($B$2:B2,B2)
I did it without an array formula also, which will make maintenance easier.=NOT($E3="")
Last edited by TheN; 11-08-2016 at 11:18 PM. Reason: more info
Here's another one...
Entered in E3:
=IF(ROWS(E$3:E3)>COUNTIF(Sheet2!B:B,RIGHT(E$1)),"",INDEX(Sheet2!A:A,MATCH(--RIGHT(E$1),Sheet2!B:B,0)+ROWS(E$3:E3)-1))
Copy down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks