Using lookup to return a value based upon multiple criteria

Question

I have a workbook that is using data validation to choose items (drop down) based upon "Description", "Size", "Unit of Duration" (i.e.; Day, Week, Month, Each), by which will return a unit price based on those selections. Example;

Add Tool Or Equipment
Description		Size	UOM	Unit Price
Extension Ladder	24 Ft.	Day

 There is a table that has ~ 160 different items to choose from for the description.  Each description has an associated "size" that can be chosen for that description as well as a "UOM".  When combined together these will return the correct "unit price".  We currently use a table with all the commonly used 60+ items (takes up a lot of space) to perform this function and would be better to have this as a "Look up and return value" solution. See the portion of that table containing the 24 Ft. Ext Ladder row below. Hope this makes sense.  thank you.

	8 Ft. Ladder			$                   -		$                   -	Cable Pulling, System			$                   -		$                       -	Doble F6150 Relay Test Set			$                           -		$                     -	FORKTRUCK FREIGHT - PER FORKTRUCK			$                                  -		$                       -
	10 Ft. Ladder			$                   -		$                   -	Cable Cutters, Hydraulic (To 1,000 MCM)			$                   -		$                       -	Doble Power Factor Set			$                           -		$                     -	FUEL TANK FREIGHT - EACH WAY			$                                  -		$                       -
	24 Ft. Ext Ladder		Day	$              3.65	2	$              7.30	Hydraulic Punch Set			$                   -		$                       -	Transformer Winding Resistance Test Set			$                           -		$                     -	ADDITIONAL TOOLS/EQUIPMENT
	Portable Generator 5000W			$                   -		$                   -	High Voltage PPE Kit			$                   -		$                       -	Turns Ratio Test Set - TTR			$                           -		$                     -	40 cal/cm2 Arc-flash Kit			$                                  -		$                       -
	Floodlight, Stand Type 750W			$                   -		$                   -	Rubber Insulated Gloves-Class 1			$                   -		$                       -	Very Low Frequency Test Set (VLF)			$                           -		$                     -	Mobile Office Trailer			$                                  -		$                       -
	Cordless Drill 1/2 inch			$                   -		$                   -	Rubber Insulated Gloves-Class 2			$                   -		$                       -	Infra-Red Camera - No Operator			$                           -		$                     -				$                                  -		$                       -

Answer 1

It is unclear exactly how your lookup table is structured - appears to have 4 sets of 4 columns, but all the data should be in 4 columns only.

The easiest way to get your desired price is to use SUMIFS. BUT, and this is a big requirement, your entries in the dropdowns need to exactly match the values in your lookup table. For example, in your top table, you have two cells:

Extension Ladder	24 Ft.

In the bottom table, you have "24 Ft. Ext Ladder" The top table should also have "24 Ft. Ext Ladder" as the entry. Or your lookup table needs to have the entries split across two columns as well.

Anyway, to return the price, you could use SUMIFS like

=SUMIFS(price column, description column, description, duration column, unit of duration)