Use 2 Different Named Cell Ranges To Return Values From 1 Of the Ranges In Formula

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

DESSTRO welcome to the forum.

Whew! That's a lot of non-contiguous named ranges. It took me a while to figure it out.

There is one named cell range that is F6:Y14 - Named "ONEFIFTY" In cell B11

Formula:

Please Login or Register  to view this content.

There is another named cell range that is Q8:Q14 - Named "SIXINCH" In cell D11

Formula:

Please Login or Register  to view this content.

Those two return the ranges in question.

In B13 and filled down until you get blanks

Formula:

Please Login or Register  to view this content.

... that will return ONEFIFTY/6IN if both arrays are the same.

=INDEX(SIXINCH,,,MATCH(D4,VALUES!$D$2:$D$8,0)) may need some explanation. If you are not aware of it there is a seldom used 4th argument in INDEX. Since SIXINCH is a non-contiguous range ... each sub range comma separated, numbered left to right 1 through n ... this finds that sub range or area_num by matching D4 to the data validation list.

Now, How can I get that to replicate throughout the entire table, i.e., how do I get values for any class and any size? If I choose 4IN Class 900 or 1IN Class 600?

If you put these non contiguous range names in another drop down I might be able to do something about that. I believe it will require INDIRECT to reference those items in the drop down. It's been years since I've done that, but set it up and I will see what I can do.

EIGHTEENINCH EIGHTINCH FIVEINCH FOURINCH FOURTEENINCH HALFINCH ONEANDONEHALFINCH ONEANDONEQUARTERINCH ONEINCH SIXINCH SIXTEENINCH TENINCH THREEANDONEHALFINCH THREEINCH THREEQUARTERINCH TWELVEINCH TWENTYFOURINCH TWENTYINCH TWOANDONEHALFINCH TWOINCH

Edit You are also going to need a separate drop down for the single range references (ONEFIFTY, etc.)

Thanks for the underscores and yes I am a retired FireFighter. Thanks for asking.

I am breaking discipline and not posting the formula here. It's 6 lines long and without context there would be no point. It is in B8:B15 of 'Sheet1'.

You might consider another named range to clear up some formula clutter.
The non contiguous range
(ONE_FIFTY,THREE_HUNDRED,FOUR_HUNDRED,SIX_HUNDRED,NINE_HUNDRED,FIFTEEN_HUNDRED,TWENTYFIVE_HUNDRED)
could be replaced with that name.

Glad to hear it! Thank you for the kind comment, the rep and marking this thread SOLVED.