Rewrite formula so 2nd, 3rd…occurrence of lookup value return blank cell

[Sean15]

Hi:

Could you help me rewrite formula in column F to allow 2nd, 3rd…occurrence of lookup value return blank cell?
Thank you for your help.

Sean

Book2
	B	C	D	E	F	G	H	I	J	K	L
2	Tom	1
3	Peter	2		Tom	1
4	Tom	3		Tom	1	2nd, 3rd…occurrence of lookup value should return blank cell
5	Peter	4		peter	2
6	Paul	5		peter	2	2nd, 3rd…occurrence of lookuo value should return blank cell
Sheet1

Cell Formulas
Range	Formula
F3:F6	F3	=INDEX($C$2:$C$6,MATCH(E3,$B$2:$B$6,0))

 

[djclements]

Try either of the following in cell F3, then copy down as needed:

=IF(COUNTIF($E$3:E3,E3)=1,INDEX($C$2:$C$6,MATCH(E3,$B$2:$B$6,0)),"")

//OR

=IF(ISERR(E3 INDEX($E$3:E3,MATCH(E3,$E$3:E3,0))),"",INDEX($C$2:$C$6,MATCH(E3,$B$2:$B$6,0)))

Please note, the space (intersection operator) between E3 and INDEX in the second formula is required for this method to work.

 

[Sean15]

I went with this formula. Thank you very much.

Excel Formula:

=IF(COUNTIF($E$3:E3,E3)=1,INDEX($C$2:$C$6,MATCH(E3,$B$2:$B$6,0)),"")

 

[Sean15]

Hi:

If lookup value is not in lookup list, Excel returns #N/A. Can formula in post #3 be adapted to return a
blank cell if lookup value does not exist?

 

[Peter_SSs]

Try

=IF(COUNTIF($E$3:E3,E3)=1,IFNA(INDEX($C$2:$C$6,MATCH(E3,$B$2:$B$6,0)),""),"")

 

[Sean15]

@Peter_SSs
My version of Excel does not have IFNA so I replaced it with IFERROR. Formula in post #5 returns blank cell for #N/A error.
I can't mark post #5 as solution as it will replace post #2 as solution.

 

[Peter_SSs]

My version of Excel does not have IFNA

Sorry about that. I thought that I checked your version but obviously didn't or didn't do it correctly.

Anyway, you fixed it up with IFERROR

I can't mark post #5 as solution as it will replace post #2 as solution.

It isn't a problem - post #2 answers the original question so it's fine to mark that.