Vlookup with multiple matches

[shre0047]

I'm attempting to create a vlookup, but the lookup value may have more than one entry. Is there a formula I can use which to

IF "lookup value" was referenced in a previous cell columns
THEN return value for 2nd,3rd,4th, etc.
ELSE return value for 1st.

Sheet1

Check	Status (returned via vlookup)
2345	Bad
1234	Good
1234	Bad
1234	Good

Data in Sheet2

Column1	Column2
1234	Good
2345	Bad
1234	Bad
3456	Good
1234	Good

 

[jasonb75]

Try this one in B1 of Sheet1, then fill down. The formula allows for data down to row 100 in sheet 2. Using full columns with this is not a good idea.

=IFERROR(INDEX(Sheet2!B:B,AGGREGATE(15,6,ROW(Sheet2!A$1:A$100)/(Sheet2!A$1:A$100=Sheet1!A1),COUNTIFS(A$1:A1,A1))),"")

 

[Fluff]

Another option

+Fluff New.xlsm
	A	B	C	D	E	F
1	Column1	Column2			Check	Status
2	1234	Good			2345	Bad
3	2345	Bad			1234	Good
4	1234	Bad			1234	Bad
5	3456	Good			1234	Good
6	1234	Good
7
Data

Cell Formulas
Range	Formula
F2:F5	F2	=INDEX(FILTER($B$2:$B$6,$A$2:$A$6=E2),COUNTIFS(E$2:E2,E2))

 

[shre0047]

The data in Sheet2 will have 300-400 rows. Will the formula create any performance issues due to the size of the dataset?

 

[Fluff]

Both formulae should be fine with that amount of data.

 

[jasonb75]

Neither should be too bad with a data set that small, you could set the ranges to 1000 rows without issues.

An entire column is over 1 million rows, to put it into perspective, if you use an entire column with 10 thousand rows then 99% of the processing effort is being wasted.

 

[shre0047]

Thank you both for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.