Auto Fill cell with information from Table

[BPN1993]

Hey,

I'm working on a job cost form to price things for my business. On the job cost form I have it set up as follows:

Size	Shape	Color	Qty	Unit Cost
5"	Round	Red	5	$0.07
11"	Round	Red	10	$0.15

<tbody>
</tbody>

I also have a sheet that has the pricing data from my main supplier. it looks as follows:

Color	+/-	Base Color	Size	Qty	Base Price	Cost	Cost Per Unit
Red	$0.00	Red	5"	100	$5.10	$5.10	$0.05
Red	$0.00	Red	11"	100	12.15	12.15	$0.12

<tbody>
</tbody>

I am wanting to get excel to take the values in the Size, Shape, and Color columns of the costing form and look them up in the database table and then grab the Cost per unit and return it in the unit cost column.

 

[Yongle]

Your data does not include Shape column, so I added it in to help illustrate

I find it easiest to use a helper column in the Data sheet to concatenate all the values that need to match
You could also add a helper column in sheet1 and simplify the formula - but I have not done that here
All that matters is that the string that is being matched is identicalt to what is in the helper column
I use " | " in the concatenation to make it easier to see
But the objective is to match string of Size&Shape&Color in sheet1 with same string in helper column in "Data"

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​
1​	Size	Shape	Color	Qty	Unit Cost	formula in E2 copied down
2​	5"	Round	Red	5​	$0.05	=INDEX(Data!H:H,MATCH(A2&" | " &B2&" | " &C2,Data!J:J,0))
3​	11"	Round	Red	10​	$0.12

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Color	+/-	Base Color	Size	Qty	Base Price	Cost	Cost per unit	Shape	Helper Column	Formula in J2 copied down
2​	Red	$0.00	Red	5"	100​	$5.10	$5.10	$0.05	Round	5" | Round | Red	=D2&" | " &I2&" | " &A2
3​	Red	$0.00	Red	11"	100​	12.15​	12.15​	$0.12	Round	11" | Round | Red

Sheet: Data