Vlookup Work Around

[spyldbrat]

I am using a vlookup, The problem is that some of data in my look up column is not unique and it's only returning the first row of data on both rows where I have my lookup. I understand that this is how vlookups work, but is there any way for it to show the second row of data if the first row has already been selected? Unfortunately, I can not make my lookup column any more "unique" than what is already there.

 

[spyldbrat]

Hello, reposting my question in hopes that someone else can answer this?

Hi..after a lot attempts, I finally got your formula to work . Is there a way that if the data does not exist in on my source tab (P Pivot), that I can have it look for the information on a second tab (R Pivot)? This is what I came up with but it's not working:

=IF(ISNA(A8="","",INDEX('P Pivot'!C$2:C$9999,AGGREGATE(15,6,ROW('P Pivot'!$C$2:$C$9999)-ROW('P Pivot'!$C$1)/('P Pivot'!$C$2:$C$9999=$C8),COUNTIF($B$1:$B8,$B8),(A8="","",INDEX('R Pivot'!C$2:C$9999,AGGREGATE(15,6,ROW('R Pivot'!$C$2:$C$9999)-ROW('R Pivot'!$C$1)/(R Pivot'!$C$2:$C$9999=$C8),COUNTIF($B$1:$B8,$B8))))