Lookup that returns only one of multiple matches based on 2-criteria

[kevsvette]

I need a look up formula (in Col. Q) that matches an “Account Name” in column AE with the corresponding “Account Name” in column N only if the “Type” in column Y matches a given value (in this case “Illustration”) & only return the associated # from column O for the first match. It will always start in row 4.

Any additional matches return “--“, so I can use a sort to drop them to bottom.

Also, any entries that do not match return an “Error”.

The table below would be before a sort which brings to the top only those entries that match with a given #.

	Col. N	Col. O		Col. Q	Col. Y	Col. AE
Row 4	Smith, Bob	1		1	Illustration	Smith, Bob
Row 5	Smith, Janet	2		--	Illustration	Smith, Bob
Row 6	Jones, Mary	3		3	Illustration	Jones, Mary
Row 7	Stewart, Tony	4		Error	Meeting	Jones, Mary
Row 8	Petty, Richard	5		--	Illustration	Jones, Mary

Right now I use a simple vlookup to find the matches & resort the table but get all the entries that returns the #, & have too many duplicate entries to deal with.

 

[wideboydixon]

Book1
	N	O	Q	Y	AE
4	Smith, Bob	1	1	Illustration	Smith, Bob
5	Smith, Janet	2	--	Illustration	Smith, Bob
6	Jones, Mary	3	3	Illustration	Jones, Mary
7	Stewart, Tony	4	Error	Meeting	Jones, Mary
8	Petty, Richard	5	--	Illustration	Jones, Mary
Sheet1

Cell Formulas
Range	Formula
Q4	=IF($Y4 = "Illustration", IF(COUNTIFS($AE$4:$AE4,$AE4,$Y$4:$Y4,"Illustration") > 1, "--", VLOOKUP($AE4, $N$4:$O$8, 2, FALSE)), "Error")

Is this along the right lines? You'll need to change $N$4:$O$8 to include the whole lookup range. "Illustration" is also hard-coded here; not sure if that matters.

WBD

 

[kevsvette]

This is close, but the Vlookup is returning #N/A when the name isn't on the lookup list. Also, I'm not sure if it matters, but the name column AE is not in alphabetical order. I can do so, but I think that might be a problem. I think having a static reference would correct it. When I get more of a chance to play with it I'll post a reply...

Thanks for you help!

 

[Aladin Akyurek]

This is close, but the Vlookup is returning #N/A when the name isn't on the lookup list. Also, I'm not sure if it matters, but the name column AE is not in alphabetical order. I can do so, but I think that might be a problem. I think having a static reference would correct it. When I get more of a chance to play with it I'll post a reply...

Thanks for you help!

What should be the desired results in column Q?

 

[kevsvette]

Post Corrected below

 

[kevsvette]

Thanks for the input Wideboydixon, your formula gave me the ideas needed to tweak the formula. Below is what I was able to do to get the desired result...

=IF(COUNTIF(N:N,AE4)0,IF($Y4="Illustration",IF(COUNTIFS($AE3:$AE4,$AE4,$Y3:$Y4,"Illustration")1,"--",VLOOKUP(AE4,$N$3:$O$312,2,FALSE)),"Matching Name, Err Type"),"No Name Match")

This is an attempt to match up only the 1st instance of a match, by name, from 2 different tables if the type in one of the 2 tables is listed as "illustration" & identify any additional duplicate matches that can be sorted out.

1st the COUNTIF checks to make sure that the Name in column AE (table 2) is even in column N (table 1). If so it continues to check if the Type is Illustration (both in table 2) as well as determines whether or not it’s the 1st instances of the name matching with a type of Illustration. If its the 1st instance, the VLOOKUP will give me the corresponding # that's in table 1 so I can resort table 2 to match up with table 1.

Along the way if the table 2 name doesn't match up with a table 1 name than it returns a No Name Match. If the Name matches but the Type isn't Illustration, than it returns Matching Name, Err Type. If the Name matches & the Type is illustration & it’s a duplicate matched entry, than it returns an "--".

Before I put in the formulas I need to sort table 2 by the Name and I further sort to get the associated Illustration record entries to the top (via cell shading). So far I've tested it & it works out fine, but I won’t give it its final test until Thursday. I'll let everyone know if it’s truly successful or not, but so far so good.

Once table 2 is sorted as above & the formulas are in place (col. Q) I resort based on column Q. Unfortunately there are name spelling differences & not always will there be a Type of Illustration for a given table 1 name thou there is a table 2 match, but that's the way it goes.

Thanks for everyone's consideration!