Need help setting up an equation

Hello!

I have to set up an equation in Excel that will allow me to input data periodically. There is fixed infomation and information that is circumstancial. I have equipment that I need to calculate the mechanical hours for. I have a code for each piece of equipment and a dedicated unit of measure for the man-hours for each piece. Equipment #316 takes 2.80 man hours per X 1000 miles. I need to set it up so that you can input the equipment number and the # of 1000's of miles and it will calculate the mechanical hours. However, I have 500 equipment codes, and they are not all measured in the same unit. Some are measured in hours, unit(annual), and then # of 1000's of miles. Since I have three (3) different units, do I need three (3) different equations? Can I set it up so that it will automatically know which unit to use based on the equipment number? Each equipment piece has a different number of man-hours. Where do I start?

Thank you so much for helping! I look forward to hearing from someone!

So I set a workbook up with a small sample of the data I need. I have about ten pages of similar data that I will eventually be incorporating with this formula. I am having issues with the lookup. When you look at the attachment, the data table is to the left and my formula is on the right. The equip code field is entered by the user. I entered a lookup formula to the right under man hours so that if you wanted equip code 645 in H5, it would give you the man hours for that equip code in I5. I now need K5 to give me the unit (i.e. miles, hours, or unit(annual)). Then I need another field that can be manually entered by the user to show how many miles, or hours, or units the equip was used for. The last area is the mechanic hours. After I can determine the man hours for the equip code, and the unit of measure, I need to find the product of man hours x unit of measure(X) = mechanic hours. X being the amount of units used by the equipment. I ultimately need to get a formulated table where two fields are constantly blank until the user enters the needed data. I need this worksheet to be able to be used by any user who knows their equip code and a number for usage. Help!

I have never used index before! I am so impressed! Thank you very much for your help. This is exactly what I needed!

In regards to the same document, I have another issue. I am importing data in the 1000s and I thought I could make it so that it populates the equation with all the data it finds and do the calculation at once but I am having trouble. So If I have 2000 equipment codes, all equipment measured in one of the three different categories, with all different man hours, can I use the same equation to calculate the mechanic hours for all at once and give me the total? Do I need to make a different sheet to populate with the data and then can the same query I have work for that many different numbers?

Okay, so attached is the full Master Data with a second tab filled with fictional data that would be imported from an outside source. My concern is that once a formula is created, the importing of the outside data will cause issues such that the formula will be inaccurate and need adjustment each time. Can the query search for specific text? So when the query is instructed to look for which unit the specific equip has, it will be able to determine if that equip code is measured in miles, hours, or units(annual)? And, some of the data could possible have information in multiple categories, for example miles and hours, but that specific equip code may only be measured in hours, so will excel be able to ignore the miles data?
The MASTER tab has the original equation that works for one equip code. The ultimate goal is to be able to import the equip code and usage data from any county, city, agency (because the equip codes are uniform across the board) and calculate the total mechanical hours used seperate from the individual query. Each county, city, agency, etc. has a different numbering system for that equipment which is irrelevant to this formula but I thought I would put it in there so that variable did not get overlooked. What if there are 1000s? I only gave an example of 100. Also, what if the information is not in the correct column order? Will I have to input it manually if it is not organized properly before import? Or can the formula search over the whole book for its required information accurately?

Specifically speaking about Equip 61, the Master depicts that it is measured in Miles. So even though on the datasheet it has information for both miles and unit(annual), we can only use the miles to calculate the mechanic hours. I was simply accounting for more information than necessary. I was hoping that there was a way Excel could filter through that information when I import it and collect the correct values needed for the equation. I was unsure how to set up that type of filter automatically so I created a data pool that had more information than necessary. The ultimate goal is that when information is imported, regardless of its orient or format, as long as the categories for the information are clearly labeled similarly to the example data sheet I want to be able to get the total mechanic hours. I can already add two values (the equip code and the usage) and get the mechanic hours for one piece of equipment. How do I manipulate that equation to get the mechanic hours for up to 1000 different equip pieces that may all possibly be measured in different categories?