Use Index Match With Choose To Return Text Data from different sheets

I have a quandary and I am unsure how to fix it ... I am creating an asset management spreadsheet that I want to make as "stupid proof" and easy to use as humanly possible. My sub contractors have to be able to do minimal work to add their data, which, when compiled, will easily reach over 10000 rows of information.

I have compiled, in one excel file, much of the basic data that I need to show up in the main spreadsheet to be populated. I have populated some of the rows with simple Data Validation lists, but there are simply far too many options to offer in these lists for all of the data. Scrolling through a hundred items to find the one item in the list you would like will take far too long. I have used vlookups to link several pieces of data but vlookup just isn't cutting it for some of the more complicated functions I want to add. I have tried to use index, match but something I am doing just isn't jiving.

It seems to me that I SHOULD be able to use a combination of either vlookup and choose, index, match (or index, match, match) or some combination of these things to get my spreadsheet to return the information I need automatically, but I can't figure out how on earth I need to combine these equations to make it happen.

SO, long story short, I have my main spreadsheet. I have a Data Validation drop down list where you can choose a certain code. Each code is associated with several attributes which are listed in a table on a different sheet. When someone chooses a particular code from the drop down, I want those attributes from the other sheet to populate into my main spreadsheet automatically.

It's getting to crunch time and I'm supposed to be submitting a draft of this spreadsheet by end of day TOMORROW and I am at my wit's end here.

I'm reasonably certain that what I want to do is possible, but I'm out of time to tinker with it myself ... Please help!!

See the attachment and let me know if you are trying to achieve this.

This should help. I had to alter my file some for confidentiality but nothing I excluded should affect what I'm asking so I've attached it here.

In the attached file, Column D (Asset Code Type) is a Validated Data dropdown list from the Sheet entitled Asset Type Code List. Column E is linked to the data in Column D via an IF statement with a Vlookup and returns data from the Asset Type Code List sheet in the cells adjacent to the cell that matches what was chosen in Column D. That's fine. The list is long but I couldn't think of a better way to do that. If anyone knows of a better way, I would love to learn about it!

To clarify what I'm asking, if I select "Compressor" from the drop down in the Asset List sheet in Column D, Column E automatically populates to "Compressor" because that is the asset type description that matches the drop down selection. When I select "Compressor" from the drop down, I also want all of my Primary Asset Attributes to populate from the sheet Asset Attributes as well. In this case, if I select Compressor in Column D, I want Columns I through P to read, TYPE, Name, CAPACITY, L/s, POWER, kW, VOLTAGE, V. (there may not be a way to return all of that data at the same time ... I'm not sure).

That worked! Thank you! Can you explain how you made that formula work? The things I was coming up with were similar to that, but I didn't think to start with an IFERROR function for whatever reason ...

This part

Please Login or Register  to view this content.

is getting the row number from Asset Attributes sheet where the the Asset Code Type is a same as D4 of Asset List sheet. Then

Please Login or Register  to view this content.

is indexing the value from that row of column D from Asset Attributes sheet. If D4 is blank or D4 don't match with any value of column A on Asset Attributes sheet then the 1st part will encounter an error. That's why I have used

Please Login or Register  to view this content.

. Now it will show blank in case of error.

Thank you! That makes sense now! Now, how do I mark this as being solved??

You are welcome.