Returning the second, third, etc. value on lookup

[sanantonio]

Hi All,

I regularly need to return not just the first and last (Which is of course easily returned via xlookup) but everything in between. I found a guide online and am attempting to follow it but can't get it to work as instructed.

The instruction said you should change the final digit in the formula from 2, to whatever entry you are wanting it to return. It works for the first and second found value but refuses to return the third:

Additionally, in an ideal world I'd get this look up to return values based on 3 criteria. I have a tried and trusted formula for that which I use often (Index/Match) but how I'd incorporate it into finding the 2nd, 3rd etc. I'm not sure and would appreciate some input. The usual formula I use for that is:

=INDEX(E3:E8,MATCH(1,(G3=B3:B8)*(G4=C3:C8)*(G5=D3:D8),0))

Thanks in advance all!

 

[Scott Huish]

Since you have Office 365:

Book1
	A	B	C	D	E
1	Products	Quantities		Banana
2	Chocolate	100		First Match	200
3	Banana	200		Second Match	300
4	Orange	500		Third Match	50
5	Banana	300
6	Peach	250
7	Banana	50
8	Banana	600
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=CHOOSECOLS(TAKE(FILTER(A2:B8,A2:A8=D1),3),2)
Dynamic array formulas.

 

[Fluff]

How about

=filter(E3:E8,(G3=B3:B8)*(G4=C3:C8)*(G5=D3:D8))

 

[sanantonio]

Since you have Office 365:

Book1
	A	B	C	D	E
1	Products	Quantities		Banana
2	Chocolate	100		First Match	200
3	Banana	200		Second Match	300
4	Orange	500		Third Match	50
5	Banana	300
6	Peach	250
7	Banana	50
8	Banana	600
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=CHOOSECOLS(TAKE(FILTER(A2:B8,A2:A8=D1),3),2)
Dynamic array formulas.

Hi @Scott Huish thanks for coming back to me.

I get mixed results with your suggestion.

Following your suggestions without locking any cells I get this:

If I lock all the cells I get:

And then if I lock D1 I get:

I presume I'm doing the locking part incorrectly, any advice?

 

[sanantonio]

How about

=filter(E3:E8,(G3=B3:B8)*(G4=C3:C8)*(G5=D3:D8))

Hi @Fluff

Thanks for coming back to me, this formula references a lot of cells that are blank in my example and returns errors. Not sure what each part is supposed to refer to in my example?

 

[Fluff]

With the formula Scott supplied do not confirm it with Ctrl Shift Enter, just use Enter & it will spill down.
Not sure what you mean about the formula I supplied referencing blank cells, it was based on the Index/match formula you posted to use various criteria.

 

[sanantonio]

Hi @Fluff

Got it both work perfectly now!

Thanks you to both.

 

[Fluff]

Glad we could help & thanks for the feedback.