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.

 

[Toadstool]

Hi Spyldbrat,

You can use the SMALL function of AGGREGATE.

SPYldbrat.xlsx
	A	B	C	D
1		SOURCE
2	Vlookup Field	Shipping	Contract No.	Etc.
3	7762-3	7878	R123	Line1
4	7762-3	7878	R456	Line2
SOURCE

SPYldbrat.xlsx
	A	B	C	D
1		RESULT
2	Lookup Column	Shipping	Contract No.	Etc.
3	7762-3	7878	R123	Line1
4	7762-3	7878	R456	Line2
5
RESULT

Cell Formulas
Range	Formula
B3:D5	B3	=IF(A3="","",INDEX(SOURCE!B$3:B$9999,AGGREGATE(15,6,ROW(SOURCE!$A$3:$A$9999)-ROW(SOURCE!$A$2)/(SOURCE!$A$3:$A$9999=$A3),COUNTIF($A$2:$A3,$A3))))

 

[spyldbrat]

When I try to type this formula it opens a window that wants me to update the values in my source data (it's a pivot). When I select the file, it changes the name of my source tab within the formula to the name of the actual file I select.

 

[Toadstool]

When I try to type this formula it opens a window that wants me to update the values in my source data (it's a pivot). When I select the file, it changes the name of my source tab within the formula to the name of the actual file I select.

I'm not sure of the effect of your pivot but make sure the data is on a tab named SOURCE and the formulae in RESULT

 

[spyldbrat]

I tried to edit my previous message but I took too long. This is what I wanted to edit it with:
When I try to type this formula it opens a window that wants me to update the values in my source data (it's a pivot). If I move the formula on the spreadsheet, it keeps wanting to update the values? Is this normal? Also, after I updated the pivot values, it returned the result of "(blank) (blank) (blank)". I am thinking that perhaps I may have some of the row #'s wrong. Would you mind showing me the formula from the first row of the data - which in in row 7. Row 6 is a header. Also, just to confirm - I should be replacing word "SOURCE" with the tab name that contains my source data?

 

[Toadstool]

I tried to edit my previous message but I took too long. This is what I wanted to edit it with:
When I try to type this formula it opens a window that wants me to update the values in my source data (it's a pivot). If I move the formula on the spreadsheet, it keeps wanting to update the values? Is this normal? Also, after I updated the pivot values, it returned the result of "(blank) (blank) (blank)". I am thinking that perhaps I may have some of the row #'s wrong. Would you mind showing me the formula from the first row of the data - which in in row 7. Row 6 is a header. Also, just to confirm - I should be replacing word "SOURCE" with the tab name that contains my source data?

Your images showed two sheets which I have assumed were in the same workbook. For my example you should have the data in a sheet called SOURCE and the formulae in a sheet called RESULT, as shown on the XL2BB output.

 

[spyldbrat]

My source and result are in the same workbook.

 

[Toadstool]

Without seeing the workbook I can't offer any further suggestions.

 

[spyldbrat]

Unfortunately, I don't have permissions so I can't download the XL2B need to upload the file.

 

[spyldbrat]

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))))