Lookup the Second, Third, or Nth Value in Excel to be zero
-
sachin483
- 2StarLounger
- Posts: 101
- Joined: 03 Feb 2018, 04:20
Lookup the Second, Third, or Nth Value in Excel to be zero
i have got 20,000 lines in data with repeated state but while doing vlookup i only want it to post on 1st vlookup found other should be zero
-
Nabeel
- 2StarLounger
- Posts: 170
- Joined: 26 Jan 2017, 07:24
Re: Lookup the Second, Third, or Nth Value in Excel to be zero
May someone will come with better approach!
=IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,ROW($B$2:$B$9)-1/($A$2:$A$9=C3),IF(COUNTIF($C$3:C3,C3)=1,1,0))),0)
=IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,ROW($B$2:$B$9)-1/($A$2:$A$9=C3),IF(COUNTIF($C$3:C3,C3)=1,1,0))),0)
You do not have the required permissions to view the files attached to this post.
-
Nabeel
- 2StarLounger
- Posts: 170
- Joined: 26 Jan 2017, 07:24
Re: Lookup the Second, Third, or Nth Value in Excel to be zero
Ah, here is much simpler approach
=IFERROR(VLOOKUP(IF(COUNTIF($C$3:C3,C3)=1,C3,""),A:B,2,0),"")
Nabeel
=IFERROR(VLOOKUP(IF(COUNTIF($C$3:C3,C3)=1,C3,""),A:B,2,0),"")
Nabeel
-
HansV
- Administrator
- Posts: 78577
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Lookup the Second, Third, or Nth Value in Excel to be zero
Or, more or less equivalently
=IF(COUNTIF(C$3:C3,C3)=1,IFERROR(VLOOKUP(C3,A:B,2,FALSE),0),0)
=IF(COUNTIF(C$3:C3,C3)=1,IFERROR(VLOOKUP(C3,A:B,2,FALSE),0),0)
Best wishes,
Hans
Hans
-
sachin483
- 2StarLounger
- Posts: 101
- Joined: 03 Feb 2018, 04:20
Re: Lookup the Second, Third, or Nth Value in Excel to be zero
thanks a lot @Nabeel & @HansV both formula working fine