Look up to return next matching value from list

[mjayz]

Hi

I trying to return a value into column B, (like a vlookup using column A) from a list in another tab but I want to get a unique result. The next one in the list. So the result would look like this...

... from a list like this

Any help is appreciated as always.

MJ

 

[kweaver]

Update your profile to indicate what version of Excel you are using as results could vary depending on the version.
Must you have a formula or would VBA work as well?

 

[kweaver]

In 365, is that what you want?

Book4
	A	B	C	D	E
1	Size A	Next Reslt		Lookup List
2	Size A	1001		Size A	1001
3	Size A	1004		Size B	1002
4	Size A	1005		Size C	1003
5	Size A	1006		Size A	1004
6				Size A	1005
7				Size A	1006
8				Size B	1007
9				Size C	1008
Sheet5

Cell Formulas
Range	Formula
A2:A5	A2	=FILTER(D2:D20,D2:D20=A1)
B2:B5	B2	=FILTER(E2:E20,D2:D20=A1,"")
Dynamic array formulas.

Book4
	A	B
1	Size C	Next Reslt
2	Size C	1003
3	Size C	1008
Sheet5

Cell Formulas
Range	Formula
A2:A3	A2	=FILTER(D2:D20,D2:D20=A1)
B2:B3	B2	=FILTER(E2:E20,D2:D20=A1,"")
Dynamic array formulas.

 

[mjayz]

Update your profile to indicate what version of Excel you are using as results could vary depending on the version.
Must you have a formula or would VBA work as well?

I've updated profile. I'm using 2013. Trying to avoid VBA if possible so a formula would be better. Thanks for responding.

 

[mjayz]

In 365, is that what you want?

Book4
	A	B	C	D	E
1	Size A	Next Reslt		Lookup List
2	Size A	1001		Size A	1001
3	Size A	1004		Size B	1002
4	Size A	1005		Size C	1003
5	Size A	1006		Size A	1004
6				Size A	1005
7				Size A	1006
8				Size B	1007
9				Size C	1008
Sheet5

Cell Formulas
Range	Formula
A2:A5	A2	=FILTER(D2:D20,D2:D20=A1)
B2:B5	B2	=FILTER(E2:E20,D2:D20=A1,"")
Dynamic array formulas.

Book4
	A	B
1	Size C	Next Reslt
2	Size C	1003
3	Size C	1008
Sheet5

Cell Formulas
Range	Formula
A2:A3	A2	=FILTER(D2:D20,D2:D20=A1)
B2:B3	B2	=FILTER(E2:E20,D2:D20=A1,"")
Dynamic array formulas.

I assume "filter" function is not available in 2013 as I can't find it in the search list??
Thank you.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	Sizes	Next Reslt		Lookup List
2	Size A	1001		Size A	1001
3	Size A	1004		Size B	1002
4	Size A	1005		Size C	1003
5	Size A	1006		Size A	1004
6	Size A	no more		Size A	1005
7	Size B	1002		Size A	1006
8	Size C	1003		Size B	1007
9				Size C	1008
10
Main

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(INDEX($E$2:$E$9,AGGREGATE(15,6,(ROW($E$2:$E$9)-ROW($E$2)+1)/($D$2:$D$9=A2),COUNTIFS(A$2:A2,A2))),"no more")

 

[mjayz]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	Sizes	Next Reslt		Lookup List
2	Size A	1001		Size A	1001
3	Size A	1004		Size B	1002
4	Size A	1005		Size C	1003
5	Size A	1006		Size A	1004
6	Size A	no more		Size A	1005
7	Size B	1002		Size A	1006
8	Size C	1003		Size B	1007
9				Size C	1008
10
Main

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(INDEX($E$2:$E$9,AGGREGATE(15,6,(ROW($E$2:$E$9)-ROW($E$2)+1)/($D$2:$D$9=A2),COUNTIFS(A$2:A2,A2))),"no more")

This works an absolute treat!! Thank you very much for your effort. Appreciated as always.

MJ

 

[Fluff]

Glad we could help & thanks for the feedback.