VLOOKUP with multiple results RETURNS text if multiple return values...? No vba...

[heathclif]

Possible?

Trying to get a non-vba solution to have a vlookup return the word "MULTI" if there are multiple RETURN values for a lookup values. Example:


[TABLE="width: 466"]
<tbody>[TR]
[TD][TABLE="width: 466"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]lookup values[/TD]
[TD][/TD]
[TD]Array[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Holga[/TD]
[TD][/TD]
[TD]小非[/TD]
[TD="align: right"]9854[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Doña[/TD]
[TD="align: right"]3023[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Doña[/TD]
[TD="align: right"]3023[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Holga[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Holga[/TD]
[TD="align: right"]382983724[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=VLOOKUP(A2,C:D,2,FALSE)[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

(!) Notice the difference between Doña and Holga. Doña has identical return values and Holga has different return values. The solution should return the text "MULTI" for Holga, but should NOT return "MULTI" for Doña (it should return '3023').

Or does anyone have a more elegant solution? Keep in the mind the dataset spans several hundred thousand lines so needs to consider resources for scale up. Many thanks in advance.

 

[heathclif]

I would guess that this is an "average" thread...for now.

 

[Jonmo1]

This seems like an odd request (doable though)

Is there going to be more after this?
Like, of the ones that return Multi, can we list each of the items (for Holga would be 2 and 382983724) ?

 

[heathclif]

That would be great to we list each of the items! But return values would have to inhabit the same cell (ie. '2' and '382983724' would have to inhabit the same cell: 2, 382983724). Possible

 

[Jonmo1]

I think that's going to be above me..

Why the objection to VBA, it would be pretty simple that way.

 

[heathclif]

Hmmm. Hence, trying to keep it simple and return "MULTI" ...

The dataset is complicated involving pivots etc...

 

[Jonmo1]

OK, so it's NOT an objective to list each of the multi results?
You only want to identify which ones have multiple ?

 

[heathclif]

Having a list is not essential. Can it be done without the list feature? Nothing more after initial?

 

[Jonmo1]

Try

=IF(COUNTIF(C:C,A2)=COUNTIFS(C:C,A2,D:D,VLOOKUP(A2,C:D,2,FALSE)),VLOOKUP(A2,C:D,2,FALSE),"Multi")

 

[heathclif]

AH! Let me see. Brb.