Formula for Matching Multiple Criteria... NEED HELP

[excel_newbie2017]

Hi all,

Below is a sample of a table on a worksheet (we'll call this one PRODUCTS):

[TABLE="width: 975"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]DESCRIPTION[/TD]
[TD]LIST PRICE[/TD]
[TD]COST PRICE[/TD]
[TD]UNIT[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/2" X 3" X 3"[/TD]
[TD]$0.62[/TD]
[TD]$0.52[/TD]
[TD]LB[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/2" X 4" X 4"[/TD]
[TD]$0.31[/TD]
[TD]$0.26[/TD]
[TD]LB[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/4" X 1 1/2" X 1 1/2"[/TD]
[TD]$72.67[/TD]
[TD]$60.56[/TD]
[TD]CWT[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/4" X 1" X 1"[/TD]
[TD]$0.54[/TD]
[TD]$0.45[/TD]
[TD]LB[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/4" X 1-1/2" X 1-1/2" @ 25' LG 6061[/TD]
[TD]$4.49[/TD]
[TD]$3.74[/TD]
[TD]LB[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/4" X 2" X 2"[/TD]
[TD]$74.65[/TD]
[TD]$62.21[/TD]
[TD]CWT[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/4" X 2" X 2" @ 20' LG[/TD]
[TD]$43.50[/TD]
[TD]$36.25[/TD]
[TD]EA[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/4" X 2-1/2" X 1-1/2"[/TD]
[TD]$54.49[/TD]
[TD]$45.41[/TD]
[TD]EA[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/4" X 3" X 3"[/TD]
[TD]$0.50[/TD]
[TD]$0.41[/TD]
[TD]LB[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/4" X 4" X 4"[/TD]
[TD]$0.31[/TD]
[TD]$0.26[/TD]
[TD]LB[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/8" X 3/4" X 3/4"[/TD]
[TD]$1.08[/TD]
[TD]$0.90[/TD]
[TD]LB[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]3/8" X 3" X 3"[/TD]
[TD]$0.72[/TD]
[TD]$0.60[/TD]
[TD]LB[/TD]
[/TR]
[TR]
[TD]B CLIPS[/TD]
[TD]304 L SS[/TD]
[TD]$8.00[/TD]
[TD]$6.67[/TD]
[TD]EA[/TD]
[/TR]
[TR]
[TD]B CLIPS[/TD]
[TD]CARBON STEEL[/TD]
[TD]$5.00[/TD]
[TD]$3.47[/TD]
[TD]EA[/TD]
[/TR]
</tbody>[/TABLE]


On a separate worksheet (we'll call this one INVOICE) I have another table that has a drop down list for the ITEM and ITEM DESCRIPTION that pull from the worksheet above. I am able to use SUMPRODUCT to successfully update the LIST PRICE and COST PRICE once the ITEM and ITEM DESCRIPTION, but I'm unable to do that with the UNIT. I've also tried various VLOOKUP combinations without any success.

Can anyone help me solve this?

[TABLE="width: 1045"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]ITEM DESCRIPTION[/TD]
[TD]LIST PRICE[/TD]
[TD]UNIT[/TD]
[TD]COST PRICE[/TD]
[TD]UNIT[/TD]
[/TR]
[TR]
[TD]ANGLE BAR[/TD]
[TD]1/4" X 1 1/2" X 1 1/2"[/TD]
[TD]$72.67[/TD]
[TD]???[/TD]
[TD]$60.56[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1351"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1351"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Recap:

1) Select from drop down list under ITEM (dependent on list from PRODUCTS worksheet)
2) Select from drop down list under ITEM DESCRIPTION (dependent on list from PRODUCTS worksheet and what was selected for ITEM)
3) The formula under UNIT should populate the unit listed for the selected ITEM and ITEM DESCRIPTION.
From the example above the UNIT populate as CWT.

 

[AhoyNC]

Try this array formula:
Formula in D19 in the example must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F
1	ITEM	DESCRIPTION	LIST PRICE	COST PRICE	UNIT
2	ANGLE BAR	1/2" X 3" X 3"	$0.62	$0.52	LB
3	ANGLE BAR	1/2" X 4" X 4"	$0.31	$0.26	LB
4	ANGLE BAR	1/4" X 1 1/2" X 1 1/2"	$72.67	$60.56	CWT
5	ANGLE BAR	1/4" X 1" X 1"	$0.54	$0.45	LB
6	ANGLE BAR	1/4" X 1-1/2" X 1-1/2" @ 25' LG 6061	$4.49	$3.74	LB
7	ANGLE BAR	1/4" X 2" X 2"	$74.65	$62.21	CWT
8	ANGLE BAR	1/4" X 2" X 2" @ 20' LG	$43.50	$36.25	EA
9	ANGLE BAR	1/4" X 2-1/2" X 1-1/2"	$54.49	$45.41	EA
10	ANGLE BAR	1/4" X 3" X 3"	$0.50	$0.41	LB
11	ANGLE BAR	1/4" X 4" X 4"	$0.31	$0.26	LB
12	ANGLE BAR	1/8" X 3/4" X 3/4"	$1.08	$0.90	LB
13	ANGLE BAR	3/8" X 3" X 3"	$0.72	$0.60	LB
14	B CLIPS	304 L SS	$8.00	$6.67	EA
15	B CLIPS	CARBON STEEL	$5.00	$3.47	EA
16
17
18	ITEM	ITEM DESCRIPTION	LIST PRICE	UNIT	COST PRICE	UNIT
19	ANGLE BAR	1/4" X 1 1/2" X 1 1/2"	$72.67	CWT	$60.56	CWT
Sheet

 

[excel_newbie2017]

THANK YOU! It worked perfectly. You're a lifesaver.

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.