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:

A B C D 1 lookup values Array 2 Holga 小非 9854 3 Doña 3023 4 Doña 3023 5 Holga 2 6 Holga 382983724 =VLOOKUP(A2,C:D,2,FALSE) 2 <tbody> </tbody>

<tbody>
</tbody>

(!) 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]

Ferris turned '30' last February btw.

 

[heathclif]

Hi Jonmo!

Your solution works perfectly for my oversimplified example. I thought it would translate well to pivot tables, but I underestimated the messiness of the pivot tables. So I rolled back to a different solution, which is just to look at any multiple VLOOKUP values...

=IF(COUNTIF($B:$B,$L4)>1,"MULTI",VLOOKUP($L4,$B:$G,6,FALSE))

Really appreciate your returning the call of the wild!

Have an awesome day!

 

[Jonmo1]

You're welcome.

 

[heathclif]

Jonmo. I just tried your solution again. IT WORKS!!!!!!

You = Veritable Genius

I = transposed one of the values

 

[Aladin Akyurek]

Does this scale up?

Row\Col	A​	B​	C​	D​	E​
1​	lookup values		Array
2​	Holga	2, 382983724​	小非	9854​	, 9854​
3​	Doña	3023, 3023​	Doña	3023​	, 3023, 3023​
4​			Doña	3023​	, 3023​
5​			Holga	2​	, 2, 382983724​
6​			Holga	382983724​	, 382983724​
7​
8​

In B2 enter and copy down:

=REPLACE(VLOOKUP(A2,$C$2:$E$6,3,0),1,2,"")

In E2 enter and copy down:

=", "&$D2&IFERROR(VLOOKUP($C2,CHOOSE({1,2},$C3:$C$7,$E3:$E$7),2,0),"")