VLOOKUP across duplicates, returning value if found in another table

[jbo165]

Hi there,

I need to do a VLOOKUP using a value that appears multiple times in a column. Instead of excel returning me the first value as the VLOOKUP result, I would like it to return the option that also exists in another table.

Here is the table where the duplicate value exists. I am looking up 54816 to retrieve a value from column D.

However, I would like to return the option from column D that is also contained in another simple table, held on another tab/sheet.

So in this case, I would like the result of the lookup to be 27091.

What should my formula be to achieve this?

Thank you very much in advance for your help!

 

[Ezguy4u]

jbo I don't know about the others, but I am having a time trying to understand your problem. I am going to recommend downloading that XL2BB. Show us the input and what you want for a result. "I am looking up 54816 to retrieve a value from column D." Which value? "So in this case, I would like the result of the lookup to be 27091." So why would 27091 be the solution? Let's get the ball rollin and start a dialog.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also be better if we could copy your sample data for testing. Could you post it again using XL2BB as suggested previously but post it after manually entering the desired result(s) so that we can be sure what that is and where it is? Add any further written explanation that you can.

 

[jbo165]

Thanks for the replies, and apologies for not being clear. It's hard to find the right language to articulate the challenge!

My formula is currently in C3 and D3. This is a plain VLOOKUP, and presently returns the first matches from Table A because of the duplicate values in A7-A10.

What I would like instead is for this lookup into table A to return the Parent Org and Parent ID that is also found in Table B. I have outlined what this should be, in the case of the data below, in the 'Desired' section.

The straight language description of this problem is: a child ID can be associated with multiple parent IDs. I need to identify the parent ID which is also contained in a separate list, and therefore complies with other criteria I have.

Thank you again!

Example.xlsx
	A	B	C	D
1	LOOKUP
2	Child ID	Child Org	Parent Org	Parent ID
3	54816	Institut de Recherche en Informatique de Toulouse	Centre National de la Recherche Scientifique	27051
4
5	DESIRED!
6	Child ID	Child Org	Parent Org	Parent ID
7	54816	Institut de Recherche en Informatique de Toulouse	Universite Toulouse III Paul Sabatier	27091
8
9	TABLE A
10	Child ID	Child Org	Parent Org	Parent ID
11	54816	Institut de Recherche en Informatique de Toulouse	Centre National de la Recherche Scientifique	27051
12	54816	Institut de Recherche en Informatique de Toulouse	Toulouse INP	27088
13	54816	Institut de Recherche en Informatique de Toulouse	Universite Toulouse III Paul Sabatier	27091
14	54816	Institut de Recherche en Informatique de Toulouse	Institut de Recherche en Informatique de Toulouse	54816
15
16	TABLE B
17	Parent ID	Parent Org
18	36900	APHM - Assistance publique - Hôpitaux de Marseille
19	26990	Centre Hospitalier Régional Universitaire de Brest - CHRU Brest
20	37045	Centre Hospitalier Universitaire de Nice - CHU Nice
21	27091	Université Toulouse III - Paul Sabatier
22	36672	Centre Hospitalier Universitaire de Nîmes - CHU Nimes
23	55052	Centre Hospitalier Universitaire de Rouen
Table 1

Cell Formulas
Range	Formula
C3	C3	=VLOOKUP(A3,A10:D14,3,0)
D3	D3	=VLOOKUP(A3,A10:D14,4,0)

 

[jbo165]

Apologies, when I write 'My formula is currently in C3 and D3. This is a plain VLOOKUP, and presently returns the first matches from Table A because of the duplicate values in A7-A10.', the duplicate values are actually A11-A14.

 

[Peter_SSs]

because of the duplicate values in A7-A10.', the duplicate values are actually A11-A14.

Are those ranges correct (especially the first one)?

In words, step us through the process of obtaining the correct result manually.