Vlookup return value only if...

[sgalligan]

Hi all,

I've been searching the internet for a solution to this but due to the key words the only solutions I'm seeing do not pertain to this situation.

I'm basically trying to aggregate a bunch of data and I want to pull unit information from one set of data into my existing set of data so I have all the information I need in one spot. The common denominators in each set of data are product codes and countries.

I have two data sets--lets call them data set 1 and data set 2. I'm trying to vlookup a product code from data set 1 to pull in a value from data set 2 but ONLY if the country values match. I'm trying to avoid creating another column to combine product code and country into one cell just for the vlookup so hoping there is a catch-all formula that can do that for me. Any ideas are appreciated!

I've stated the value in RED in data set 1 below.

DATA SET 1:

CODE	MEXICO	AUSTRALIA
123	Vlookup product code 123 to pull in "UNITS" value ONLY if country is Mexico	Vlookup product code 123 to pull in "UNITS" value ONLY if country is Australia

DATA SET 2:

COUNTRY	PRODUCT CODE	UNITS
MEXICO	123	5
AUSTRALIA	123	10
MEXICO	456	12

 

[etaf]

you can combine in a vlookup or index match uses &

=index( column to return , match ( cell with country & cell with code , range with country&range with code , 0 ))

see here

Book2
	A	B	C
1	CODE	MEXICO	AUSTRALIA
2	123	5	10
3
4	DATA SET 2:
5	COUNTRY	PRODUCT CODE	UNITS
6	MEXICO	123	5
7	AUSTRALIA	123	10
8	MEXICO	456	12
Sheet1

Cell Formulas
Range	Formula
B2:C2	B2	=INDEX($C$6:$C$8,MATCH($A2&B1,$B$6:$B$8&$A$6:$A$8,0))