Formula to retrieve item cost based on two variables in database

[dwsteyl]

Hi guys

I'm sure this question must have been asked before, but I couldn't find a solution searching previous answers.

I have an Excel database like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Initial[/TD]
[TD]Name[/TD]
[TD]Item1[/TD]
[TD]Item2[/TD]
[TD]Item3[/TD]
[TD]Item4[/TD]
[TD]Item5[/TD]
[/TR]
[TR]
[TD]dws[/TD]
[TD]Dietrich[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]mz[/TD]
[TD]Mary[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]35[/TD]
[TD]45[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]im[/TD]
[TD]Ingrid[/TD]
[TD]30[/TD]
[TD]36[/TD]
[TD]46[/TD]
[TD]56[/TD]
[TD]66[/TD]
[/TR]
</tbody>[/TABLE]

I need to retrieve the value of an item based on input in two cells, like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]mz[/TD]
[TD]Item3[/TD]
[TD]??[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula in the third cell that can cross reference the initial ("mz" in this case) with the item code ("Item3") and pull through the correct value (in this case "35") to cell 3.

In my real scenario I have about 12 employees with approximately twenty items. I therfore assume that the formulas can be used to reference to a range (the table in this instance), but this is not a neccesity (only an option to make the formulas easier).

Thank you for your help!

Regards
Wessel

 

[FDibbins]

Maybe something like this?
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Initial[/td][td]Name[/td][td]Item1[/td][td]Item2[/td][td]Item3[/td][td]Item4[/td][td]Item5[/td][/tr]

[tr][td]

2​

[/td][td]dws[/td][td]Dietrich[/td][td]10[/td][td]20[/td][td]30[/td][td]40[/td][td]50[/td][/tr]

[tr][td]

3​

[/td][td]mz[/td][td]Mary[/td][td]20[/td][td]25[/td][td]35[/td][td]45[/td][td]55[/td][/tr]

[tr][td]

4​

[/td][td]im[/td][td]Ingrid[/td][td]30[/td][td]36[/td][td]46[/td][td]56[/td][td]66[/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]mz[/td][td]Item3[/td][td]35[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
C8=INDEX($C$2:$G$4,MATCH(A8,$A$2:$A$4,90),MATCH(B8,$C$1:$G$1,0))

 

[dwsteyl]

FDibbins

That works perfectly! Thank you very much for the help.

Regards
dwsteyl

 

[FDibbins]

Happy to help