Dependent Percentile Formula in Table with Nested Lookup or Index Match?

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

Manually mockup some desired results to help explain what you're trying to do visually.

By the sounds of it you want to use a subset of SheetB!AA based on DD2 value which is presumably stored in another (unspecified) column on SheetB ?

If we assume the "State" data may not be stored in a contiguous manner on SheetB then an Array is probably your best bet:

Please Login or Register  to view this content.

where Col A holds State and AA the data points to be used in the Percentile.

Your problems stem from the fact that

a) "One Voice Industry" is not repeated per transaction

b) Your Validation Options don't actually match the entries on Extract sheet
(eg Engineering and Construction vs Engineering & Construction)

c) per b) your "Benchmark" values don't match the headers on Extract sheet
(eg if these columns can move it would be an idea to store these values in an adjacent col. so as to permit dynamic lookup)

You can do what you want without too much difficulty should you address all of the above
(you add a further IF and INDEX call to the Array)

Figured I should have elaborate with an example...

Step 1: Change Engineering and Construction to Engineering & Construction ('SIC Table'!B10)

Step 2: Add Key to Extract sheet

Please Login or Register  to view this content.

Step 3: Store Column References for Each Benchmark

Please Login or Register  to view this content.

Step 4: Apply the Result Array

Please Login or Register  to view this content.

Step 5: Going Forward
correct all SIC Table references such that they match those used on the Extract Sheet

In terms of DV vs ComboBoxes... I'd stick with the former.

In terms of getting the DV to work correctly... rather than lots and lots of defined names you could just do the following:

Please Login or Register  to view this content.

Now refine your 2nd DV Source on Sheet3 such that

Please Login or Register  to view this content.

I've not checked to see if you have B3:B24 etc named already - if so just use the existing name in the above formula