Vlookup multiple criteria return unique values

[dunay]

Hello,

I am new in the forum and I am looking for a quick help. I have huge data with several vlookup data which is ok but in one of the worksheets, i have repetitions which causes mistake when use vlookup. Please see the sample file. In the first page, I have ID, Country and Product and second page code, Country, approval and Product. I would like to return ID from first page. However, since I have several codes (lines) for some country/product combination Vlook up did not work. I would like to have unique ID from first page only once for the same country / product combination. If it can put the unique id in E column against approval (approved) and leave the other lines blank, that would be great. Many thanks for your support.

[alansidman]

If what you are looking to do is below then...

Excel 2016 (Windows) 32 bit

	G	H	I	J	K
4	Table3.Code	Country	Product	Table3.approval	ID
5	X	Morocco	A	N/A	1
6	Y	Morocco	A	approved	1
7	Z	Morocco	A	N/A	1
8	P	Germany	B	approved	2
9	I	Israel	D	approved	3
10	L	Morocco	F	approved	4
11	K	Algeria	A	N/A	5
12	ZZ	Italy	F	N/A	6
13	W	Egypt	B	N/A	7
14	R	Senegal	G	N/A	8
15	U	Algeria	H	approved	9
16	P	Algeria	H	N/A	9
17	O	Morocco	H	N/A	10
18	T	Germany	B	N/A	2

Sheet: Result

...here is the Mcode for a Power Query solution. Joining the two tables in an outer join.

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[dunay]

Hi,

Thanks for quick response. However, what I am looking is to get unique id (1 just one time for Morocco and Product A and the rest should be blank in K column. If the unique id, 1 against 'approved' that would be great (like below);

Table3.Code Country Product Table3.approval ID
X Morocco A N/A
Y Morocco A approved 1
Z Morocco A N/A

[Glenn Kennedy]

Use this:

=IF(D5="Approved",INDEX(sample!$A$4:$A$13,INDEX(MATCH(1,((sample!$B$4:$B$13=B5)*(sample!$C$4:$C$13=Result!C5)),0),0)),"")

In Belgium, you will probably need ; as the separator

[dunay]

Super, it worked with I was looking for but I tried it for different set of data, it is not working. I know sometimes simple things are making big difference. What is the trick here to be careful? I even checked the format of the cells from which I am getting data. I even copy/paste the data to sample file to make sure that I am not doing mistake but it did not work. It worked when I make manual entries. I have 10000 lines. Unfortunately, I cannot share the data and I know my explanation is not sufficient but if you have and idea, please comment.
Many Thanks

[alansidman]

If I am understanding your request then

Excel 2016 (Windows) 32 bit

	G	H	I	J	K
4	Table3.Code	Country	Product	Table3.approval	id.1
5	X	Morocco	A	N/A
6	Y	Morocco	A	approved	1
7	Z	Morocco	A	N/A
8	P	Germany	B	approved	2
9	I	Israel	D	approved	3
10	L	Morocco	F	approved	4
11	K	Algeria	A	N/A
12	ZZ	Italy	F	N/A
13	W	Egypt	B	N/A
14	R	Senegal	G	N/A
15	U	Algeria	H	approved	9
16	P	Algeria	H	N/A
17	O	Morocco	H	N/A
18	T	Germany	B	N/A

Sheet: Result

and the Mcode would be

Please Login or Register  to view this content.

[Glenn Kennedy]

As you say... I cannit comment on what I cannot see. Check for stray spaces in your data. That's a common problem.

[dunay]

Thanks to both! The formula worked!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.