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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Size[/TD]
[TD]Shape[/TD]
[TD]Color[/TD]
[TD]Qty[/TD]
[TD]Unit Cost[/TD]
[/TR]
[TR]
[TD]5"[/TD]
[TD]Round[/TD]
[TD]Red[/TD]
[TD]5[/TD]
[TD]$0.07[/TD]
[/TR]
[TR]
[TD]11"[/TD]
[TD]Round[/TD]
[TD]Red[/TD]
[TD]10[/TD]
[TD]$0.15[/TD]
[/TR]
</tbody>[/TABLE]

I also have a sheet that has the pricing data from my main supplier. it looks as follows:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Color[/TD]
[TD]+/-[/TD]
[TD]Base Color[/TD]
[TD]Size[/TD]
[TD]Qty[/TD]
[TD]Base
Price[/TD]
[TD]Cost[/TD]
[TD]Cost
Per Unit[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]$0.00[/TD]
[TD]Red[/TD]
[TD]5"[/TD]
[TD]100[/TD]
[TD]$5.10[/TD]
[TD]$5.10[/TD]
[TD]$0.05[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]$0.00[/TD]
[TD]Red[/TD]
[TD]11"[/TD]
[TD]100[/TD]
[TD]12.15[/TD]
[TD]12.15[/TD]
[TD]$0.12[/TD]
[/TR]
</tbody>[/TABLE]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Size[/td][td]Shape[/td][td]Color[/td][td]Qty[/td][td]Unit Cost[/td][td] formula in E2 copied down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]5"[/td][td]Round[/td][td]Red[/td][td]

5​

[/td][td]$0.05[/td][td] =INDEX(Data!H:H,MATCH(A2&" | " &B2&" | " &C2,Data!J:J,0))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]11"[/td][td]Round[/td][td]Red[/td][td]

10​

[/td][td]$0.12[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Color[/td][td]+/-[/td][td]Base Color[/td][td]Size[/td][td]Qty[/td][td]Base
Price[/td][td]Cost[/td][td]Cost
per unit[/td][td]Shape[/td][td]Helper Column[/td][td]Formula in J2 copied down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Red[/td][td]$0.00[/td][td]Red[/td][td]5"[/td][td]

100​

[/td][td]$5.10[/td][td]$5.10[/td][td]$0.05[/td][td]Round[/td][td]5" | Round | Red[/td][td=bgcolor:#D9E1F2] =D2&" | " &I2&" | " &A2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Red[/td][td]$0.00[/td][td]Red[/td][td]11"[/td][td]

100​

[/td][td]

12.15​

[/td][td]

12.15​

[/td][td]$0.12[/td][td]Round[/td][td]11" | Round | Red[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Data[/td][/tr][/table]