Finding a value based on a drop down list value obtained by an =INDIRECT formula

I have a spreadsheet called "Master File.xltx".
In the workbook are the following sheets:
Express
Codes
Lists


In the Express sheet I have a place where I can look up codes , cell A20 (based on a named range on the lists sheet), the description B20 (using a VLOOKUP based on a table on the codes sheet), and a drop down list G20 (using =INDIRECT validation list). All of this works great. However, I now need to place a value in J20 based on the value in G20. In my LISTS worksheet I have numerous named ranges that is used for the G20 indirect. I then inserted a new column next to each list so that the value in each row of the list now has a secondary value in the column next to it.

So to clarify K1-K5 is my named range that returns a value in G20 on the Express sheet. I inserted a new column (L), and placed a value next to each item in the list in column K. So back on the Express sheet I want to have J20 return the value in column L, next to the item in the list in column K.

I tried INDEX and MATCH, but I am getting a #N/A error, which I attribute to the array in the Match function. The formula is looking for a single row, and a range that is in only one column, but I have several rows and several columns for my array to look into. I was hoping to use another =INDIRECT function, but I got an error too. Any ideas?

For each code, there is only one description, hence the VLOOKUP. The fixes listed in the table are only there for reference for me to fill out the lists on the Lists tab. But there will be multiple fixes per code (hence the drop down list), and for each fix there will be a fail number. So the Fail number needs to be a combo of the code number and the specific fix. As noted before, the lists I have now are only for place holders, but they will be expanding once I finish entering in all the data.

SaaaaweeeeT!!! There was no way I was gonna figure that one out on my own, LOL. It works great!

Thank you so much for the quick help, I've been digging at this for hours!

Turns out I ran into a problem once I entered in all my data. See the attached file and the active cell should be returning a value other than what it is there. I played with some of the numbers in the formula, but I wasn't able to get it to produce the correct result. Most of the other codes are working fine, but once I get a list that's too long, it starts to provide errors. Do you mind taking another look at this for me?

That is probably the longest formula I have ever seen, how you were able to wrap your brain around something that long is amazing! It does work now though, so thank you once again for your assistance!

I wasn't sure if I should reactivate this tread, or link it and start a new one, but what I am trying to do requires all of the above information as reference, although some of the information has been somewhat changed.

Just to help summarize what I have that is working:

File is 06-07 1500 Express.xltm

Worksheet is 06-07 1500 Express

A20 is a validation list based off information in table "Codes" (table header "Code") on sheet Enginecodes.
B20 is a VLOOKUP function based on the same table used for A20 (table header "Desc")
C20 is a validation list using the =INDIRECT(A20) function. The named range is located on sheet "Lists". I have several lists, and the overall range for all lists is L1:EG15

Here is what I need:

G20 I need a drop down list based on the selection in C20.

I tried another indirect using a different list but I get an error. I am not entirely sure how to best format my data source (lists or a table) in order to best retrieve this information. The giant formula in the last few posts worked great for a cell value, but now I need a drop down list.

What it all looks like is:

A20 = Engine Code
B20 = Description
C20 = Tests
G20 = Fixes based on selected tests

The previous links to attached files should still be relevant to this post.

Let me know if you require any further clarification.

Sorry for the confusion, I forgot which file I was working on. I have created a new post here:

http://www.excelforum.com/showthread...t=#post4335035

I will set this post to being solved again.