VLookup Issue with returning value if multiple

[ronie85]

Hi, I am hoping someone can rewrite my Vlookup formula.

The formula is currently =IFERROR(VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE),"")
And the purpose of the formula is to search for a PO Number (B176) within the range of Material Purchasing'!B2:B999999 and return a value Material Purchasing'!E2:E999999, which it does.
However, this range Material Purchasing'!E2:E999999 may contain more than 1 value for which I would prefer the original =IFERROR(VLOOKUP(B176,'Material Purchasing'!$B$2:$E$999999,4,FALSE),"") formula to show an error for which I can then check it manually.

Would someone know of an easy fix for which it will only return a value if Material Purchasing'!E2:E999999 only has one output and not multiple?

 

[etaf]

i'm thinking as you have 365 version

=IFERROR(ROWS(UNIQUE(FILTER(B3:E8,B3:B8="PO-10110")))=1,"")
with AN IF()

=IFERROR(IF(ROWS(UNIQUE(CHOOSECOLS(FILTER('Material purchasing'!$B$2:$E$999999,'Material purchasing'!$B$2:$B$999999=B176),1,4)))=1,VLOOKUP(B176,'Material purchasing'!$B$2:$E$999999,4,FALSE),"ERROR"),"")

should work

Formula in D176 - just for simplicity

if B176 - DOES NOT EXIST , Then a blank
otherwise AND ERROR if B176 and E176 not all the same

Book1
	A	B	C	D
173
174
175
176		PO-10110		ERROR
177
178
Supplier Payments

Cell Formulas
Range	Formula
D176	D176	=IFERROR(IF(ROWS(UNIQUE(CHOOSECOLS(FILTER('Material purchasing'!$B$2:$E$999999,'Material purchasing'!$B$2:$B$999999=B176),1,4)))=1,VLOOKUP(B176,'Material purchasing'!$B$2:$E$999999,4,FALSE),"ERROR"),"")

Book1
	A	B	C	D	E
1
2
3		PO-10110	1	8	JN1089
4		PO-10110	2	9	JN1089
5		PO-10110	3	7	JN1089
6		PO-10110	4		JN1089
7		PO-10110	PO-10110		x
8					JN1089
9
Material purchasing

the sample spreadsheet will only be on dropbox for a few days

Dropbox

www.dropbox.com