Using Small K across multiple sheets

[eagleadam89]

Hi all

I am trying to do a lookup based on a dropdown box that will list sub categories using index and small if,

I have attached a workbook if this helps,

the lookup values are in a separate tab called lookups and the data tab is where I would like to return the data the problem I am having is it looking up values from the different worksheet and also not returning the values that are actually in the lookup,

If need any further info then let me know,

thanks all

[Olly]

Why not just use a pivot table?

See attached example.

[eagleadam89]

Hi

once I have the list I need to add an additional report to the right of it. otherwise yes I would use pivot table.

Thanks

[Olly]

Hi

once I have the list I need to add an additional report to the right of it. otherwise yes I would use pivot table.

Thanks

You can still add other content to the right of your pivot table, you know...

[eagleadam89]

yes it's just my preference to be honest

[Olly]

yes it's just my preference to be honest

But you don't know how to make your preference work!

I don't understand people's reluctance to use pivot tables, and to try to construct complex alternatives. The pivot functionality does eexactly what you want in a way that is efficient, sustainable and scalable. Why do you have a preference to try and engineer a more complex solution?!

[samba_ravi]

see attached file

[Tony Valko]

Try this array formula**:

=IFERROR(INDEX(Lookups!B:B,SMALL(IF(Lookups!A$2:A$47=E$1,ROW(Lookups!A$2:A$47)),ROWS(B$4:B4))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Entered in B4 and copied down until you get blanks.