Vlookup with multiple results

[Itsweaver]

Morning all,

Hopefully someone will be able to let me know if this is possible.

I am currently trying to create a Vlookup that has a possibility of multiple results. The cell it returns could be one of a few. Vlookup normally brings back the first result which is normally fine.

I would like a formula the would let me know if there are multiple different results but would also bring through an answer if all the possible results are the same.

Eg -

A B
1 1
2 1
1 2
1 1
1 2

If i do a vlookup on the above asking to look in column A for 1s i would like a message "multiple results" but if i was looking for 2s it would give me "1".

Hope that makes sense. I have done many searches but can not find anything like this.

Is it possible?

 

[Youngdand]

Morning all,

Hopefully someone will be able to let me know if this is possible.

I am currently trying to create a Vlookup that has a possibility of multiple results. The cell it returns could be one of a few. Vlookup normally brings back the first result which is normally fine.

I would like a formula the would let me know if there are multiple different results but would also bring through an answer if all the possible results are the same.

Eg -

A B
1 1
2 1
1 2
1 1
1 2

If i do a vlookup on the above asking to look in column A for 1s i would like a message "multiple results" but if i was looking for 2s it would give me "1".

Hope that makes sense. I have done many searches but can not find anything like this.

Is it possible?

Does it need to be specifically a vlookup?

You could use conditional formatting to identify duplicates and highlight them instead.

Select range,

Click conditional formatting on the home menu, choose highlight cell rules, then click duplicate values.

Vlookup will only return the value of the first instance it finds, so furthermore from your example, it would never find 1 2 if the lookup criterea was 1 as it would stop after finding 1 1.


Regards,

Dan.

 

[mrhstn]

You could use countif to test for multiple results

=IF(COUNTIF($A$2:$A$10, 1)>1, "Multiple Results", VLOOKUP(1, $A$2:$B$10, 2, 0))

 

[FormR]

I would like a formula the would let me know if there are multiple different results but would also bring through an answer if all the possible results are the same.

Hi, welcome to the board!

Here is one possible option..

Excel 2013/2016
	A	B	C	D	E
1	A	B		Lookup	Formula
2	1	1		1	Multiple
3	2	1		2	1
4	1	2		3	4
5	1	1
6	1	2
7	3	4
8	3	4
9	3	4
Sheet1

Cell Formulas
Range	Formula
E2	=IF(COUNTIFS($A$2:$A$9,D2)=COUNTIFS($A$2:$A$9,D2,$B$2:$B$9,VLOOKUP(D2,$A$2:$B$9,2,0)),VLOOKUP(D2,$A$2:$B$9,2,0),"Multiple")

 

[Youngdand]

Removed

 

[Itsweaver]

Hi, welcome to the board!

Here is one possible option..

Excel 2013/2016

	A	B	C	D	E
1	A	B		Lookup	Formula
2	1	1		1	Multiple
3	2	1		2	1
4	1	2		3	4
5	1	1
6	1	2
7	3	4
8	3	4
9	3	4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =IF(COUNTIFS($A$2:$A$9,D2)=COUNTIFS($A$2:$A$9,D2,$B$2:$B$9,VLOOKUP(D2,$A$2:$B$9,2,0)),VLOOKUP(D2,$A$2:$B$9,2,0),"Multiple") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

This has done the job perfectly! you have saved me many hours of pain.

Thank you !