• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

three column lookup with multiple occurance

T

tarun_1

New Member
Hi,

I have 100s of rows of tabular data.

Requirement is - user will choose the muliple drop down in filter and as per selection the result should populate.

Wondering if this is possible in excel?

Out of huge data - as per selection (assuming underlined are selection) - can the result be popluated:
ActorMovieMonthSingerRegion
Shah,Movie no5MarAsha, Shobha,Denmark,
Akshay, Amitabh, ShahMovie no6AprLata, Shobha, Asha,Denmark

Kindly refer attached file. Hope, i am able to explain my question.

-regards,
Tarun
 

Attachments

  • Chandoo Query.xlsx
    11.1 KB · Views: 4
Tarun,

Your sample file is really good. You have to use formula like below to arrive at movie name (array formula CTRL+SHIFT+ENTER):
=IFERROR(INDEX($C$3:$C$12,SMALL(IF(ISNUMBER(SEARCH($C$15,$B$3:$B$12)+SEARCH($C$16,$E$3:$E$12)+SEARCH($C$17,$F$3:$F$12)),ROW($C$3:$C$12)-2,9E+307),ROWS($A$1:A1))),"")
Note the red colored -2 adjustment as data begins at row 3. It will always be n-1 depending on where your real life data begins.

Then using it (movie name) you can look up the rest either using INDEX+MATCH or VLOOKUP.

I am attaching the file for your reference. Refer range J21:N34 for formula implementation.
 

Attachments

  • Copy of Chandoo Query.xlsx
    13 KB · Views: 5
r2c2 said:
@tarun_1

If you are using Excel 2013 or above, you can set up slicers on the table columns and use them to instantly filter the raw data. This will be very user friendly too.
Click to expand...
-----------------

Hi,
Though this is interesting, but the problem is with combined names like - 'Amitabh, Shah' and if i slice for "Shah", then this row element is skipped.

Regards,
Tarun
 
shrivallabha said:
Tarun,

Your sample file is really good. You have to use formula like below to arrive at movie name (array formula CTRL+SHIFT+ENTER):
=IFERROR(INDEX($C$3:$C$12,SMALL(IF(ISNUMBER(SEARCH($C$15,$B$3:$B$12)+SEARCH($C$16,$E$3:$E$12)+SEARCH($C$17,$F$3:$F$12)),ROW($C$3:$C$12)-2,9E+307),ROWS($A$1:A1))),"")
Note the red colored -2 adjustment as data begins at row 3. It will always be n-1 depending on where your real life data begins.

Then using it (movie name) you can look up the rest either using INDEX+MATCH or VLOOKUP.

I am attaching the file for your reference. Refer range J21:N34 for formula implementation.
Click to expand...

-----------------

Hi,

Wow... you are Ninja....

Regards,
Tarun
 
tarun_1 said:
--------------
Hi Narayan,

Though it works, but how to clear/ refresh filter every time?

Regards,
Tarun
Click to expand...
Hi ,

Clearing / reapplying the filter , is a matter of the following keystrokes :

To clear the filtered data , clear the range I21:M???? , where the endpoint can be any row of your choice.

To reapply the filter , after you have changed the criteria :

1. Click on Advanced in the Data tab.

2. Click on Copy to another location.

3. Click on OK. This assumes that the range references of the input data , the criteria or the output data have not been changed.

Reapplying the filter automatically clears the output data range of existing data , so that if the new criteria are going to result in fewer rows of output data , the additional rows of existing data will be cleared before the fresh output is displayed.

Narayan
 
You must log in or register to reply here.
Back
Top