Return Value with Multiple Criteria

[Macro212]

I would like to use a formula to return a value based on multiple criteria from multiple tabs. I would also like the formula to be built in a way that it automatically updates if there are any changes to the criteria.

Example:

Three tabs in excel:


Tab # 1 - Complete Data Set

Column A has people's name (rows A1-A20)
Column B has the values True or False for Hair (row B1-B20)
Column C has the values True or False for Eyes (row C1-C20)
Column D has the location US or non-US (row C1-C20)
Column E - In this column I want to essentially say: If the value in Column B=True, and the value in column C= Non-US, then return the value in the tab#2 for Non US values (column B), if the value in column C is True and the value in Column D is US, then return the values in Tab #3 for US values (column B).

Tab #2 - Non US Values
Column A - people's names (rows A1-A20)
Column B - Non-US values (rows B1-B20)

Tab #3 US Values
Column A - people's names (Rows A1-A20)
Column B - US values (rows B1-B20)

Can this be done?

 

[Scott T]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	name1	TRUE	TRUE	us	7				name1	1		name1	7
2	name2	TRUE	TRUE	us	7				name2	1		name2	7
3	name3	TRUE	FALSE	us	FALSE				name3	1		name3	7
4	name4	TRUE	TRUE	us	7				name4	1		name4	7
5	name5	FALSE	TRUE	us	7				name5	1		name5	7
6	name6	TRUE	TRUE	us	7				name6	1		name6	7
7	name7	TRUE	TRUE	us	7				name7	1		name7	7
8	name8	TRUE	TRUE	non-us	1				name8	1		name8	7
9	name9	TRUE	TRUE	us	7				name9	1		name9	7
10	name10	TRUE	TRUE	us	7				name10	1		name10	7
11	name11	TRUE	TRUE	us	7				name11	1		name11	7
12	name12	TRUE	TRUE	us	7				name12	1		name12	7
13	name13	TRUE	TRUE	us	7				name13	1		name13	7
14	name14	TRUE	TRUE	us	7				name14	1		name14	7
15	name15	TRUE	TRUE	us	7				name15	1		name15	7
16	name16	TRUE	TRUE	us	7				name16	1		name16	7
17	name17	TRUE	TRUE	us	7				name17	1		name17	7
18	name18	TRUE	TRUE	us	7				name18	1		name18	7
19	name19	TRUE	TRUE	us	7				name19	1		name19	7
20	name20	TRUE	TRUE	us	7				name20	1		name20	7
Sheet2

Cell Formulas
Range	Formula
E1	=IF(AND(B1,D1="non-us"),VLOOKUP(A1,$I$1:$J$20,2,0),IF(AND(C1,D1="us"),VLOOKUP(A1,$L$1:$M$20,2,0)))

copy down. If non of the criteria are met then it returns false. This can be changed to return something else.