Formula to return only one value based on criteria

[Godwin117]

I have below formula I can't seem to get to work and can't find anything online about it. A brief explanation of it is: If data from B5:B50 is equal to AUTH!A:A, it'll continue otherwise it will return blank. <It only needs to match one instance of that data, not every instance of the data. > If data from AUTH!E:E is already in F:F, then have it blank, otherwise lookup data from AUTH!A:E using $B$5:B$50 data to find the data in column 5. Any help will be greatly appreciated.

Formula is below:

=IF($B$5:$B$50 = AUTH!A:A, IF(SUMPRODUCT((AUTH!E:E = F:F))>1, "", VLOOKUP($B$5:$B$50, AUTH!A:E, 5, FALSE))"")

 

[Eric W]

I found your description to be confusing, which is probably why you haven't had any replies so far. This is a case where an example would help. In any case, given this sheet named AUTH:

Book2
	A	B	C	D	E
1	A Header	B Header	C Header	D Header	E Header
2	a	aa	aaa	aaaa	aaaaa
3	b	bb	bbb	bbbb	bbbbb
4	c	cc	ccc	cccc	ccccc
5	d	dd	ddd	dddd	ddddd
6	e	ee	eee	eeee	eeeee
7
AUTH

I came up with this basic IFERROR(VLOOKUP formula:

Book2
	A	B	C
1
2
3
4		My Codes	Result
5		b	bbbbb
6		o
7		x
8		c	ccccc
9		a	aaaaa
10		r
11		s
12
Sheet8

Cell Formulas
Range	Formula
C5:C11	C5	=IFERROR(VLOOKUP(B5,AUTH!$A$2:$E$6,5,FALSE),"")

It does mostly what you want, although I couldn't figure out what you wanted to do with column F. Let us know how close this is to your actual request.