Data Validation in 2 cells will return a value in a 3rd cell

[KrisF]

Hi, I am trying to figure out how to do this.....

I have a list of Job Roles, Locations, and a table that has a rate by role/location. I want to select the job role and the location (2 separate cells) and in a 3rd cell it will return the dollar amount for the location and job role that I selected in the first 2 cells.

I have googled this for over an hour and can't figure it out.

Any help is greatly appreciated.

Thanks
Kris

 

[Eric W]

Welcome to the forum!

You haven't given us a lot of details to work with. But consider this:

book1 work.xlsm
	A	B	C	D	E	F	G	H
1	Role	Location	Amount			Role	Location	Amount
2	Foreman	C	3			Foreman	A	1
3						Foreman	B	2
4						Foreman	C	3
5						Foreman	D	4
6						Boss	A	5
7						Boss	B	6
8						Boss	C	7
9						Boss	D	8
10						Peon	A	9
11						Peon	B	10
12						Peon	C	11
13						Peon	D	12
Sheet9

Cell Formulas
Range	Formula
C2	C2	=SUMIFS($H$2:$H$13,$F$2:$F$13,A2,$G$2:$G$13,B2)

If your dropdowns are in A2 and B2, and you have a table of Roles/Locations like I created in F1:H13, then you can use a SUMIFS formula to find the amount.