Help with VLOOKUP Table array and DV lists conflict

[RJSIGKITS]

Hi Guys - I really need some help with trying to get a VLOOKUP search to return data from a table with the name matching what is selected from a DV list drop down cell - I've tried to attach a mini example of what it is I'm trying to create but it won't allow me to add attachments...

So, I have sheets named Fusion, Fusion Linear, Biography, Caledonia and Caledonia In-Frame. Each one is saved as a Table with the same name, which needs to be searched with VLOOKUP - The table to search needs to be determined by a previous cell selection.

The user will select which range they want to be quoting in Cell C4. Cell C5 Should then return the available door options from the DV list which matches the title in C4.

When entering item codes into B13,B14,B15 etc, C13,C14,C15 etc need to return the result for that code from the tabe specified in C4...

For example -
Cell C4 - User Selects the range (DV list 'Ranges' is a list of headers on my Data Validation list sheet, and the available door options for that range are
Cell C5 - User Selects the door finish (DV List uses '=INDIRECT(SUBSTITUTE($C$4," ",""))'
Cell B13 - User enters product code
Cell C13 - Returns the result (If B13 is blank, then blank, otherwise Vlookup B13 on Range Table matching C4 returning the matched result for Column 2)

I currently have:
A sheet with My quote form.
A sheet for my DV lists consisting of Range name Headers (Being used as DV list for C4) with the available door options (DV list for C5) listed under the range names.
5 Sheets named Fusion, Fusion Linear, Biography, Caledonia and Caledonia In-Frame. Each one is saved as a Table with the same name.

The main problem I'm having is that both the DV list selection for C5 and the range name tables rely on the same name selected in Cell C4, and I can't have a DV list and a table with the same names, can I?...

I hope this makes sense, and that someone can help, as I've been wrestling with this for ages, and I'm not getting anywhere!

 

[iliace]

I tried creating an example based on what you are describing, and I am not having any issues.

There might be some confusion of terminology. Are your tables actually saved as Tables? If so, why do you need a separate name for the DV List? You can just use INDIRECT to use the existing table.

One thing you may want to change is use INDEX to get the correct column of your DV list from the Table name. For example, this is a list validation rule that uses just the first column of the Table named in H1:

=INDEX(INDIRECT($H$1), 0, 1)