Index((match(),match()) greater than issues

[Afro_Cookie]

Hi everyone,

I've looked and found several posts about using the greater than match type in index(match formula's but none have any with 2 match references and I'm stuck. I am building an AQL inspection table and want the sample size to be automatically indicated for our team however the value always drops down one level. I want it to go to the next greatest value but can't find a way to do it. Can anyone please help me out?

Book1
	C
10
Sheet1

 

[shinigamilight]

THe sample data you posted is not visible. Try again

 

[Fluff]

@Afro_Cookie you need to select a range of cells, not just a single cell.

 

[Afro_Cookie]

@Afro_Cookie you need to select a range of cells, not just a single cell.

changing the range in the matches turns the results into a spill formula, but returns the same results as though I selected a single cell and copied the formula down my table.

 

[Fluff]

You need to select all the cells with the data, before clicking on the mini-sheet, not just a single cell.

 

[Afro_Cookie]

You need to select all the cells with the data, before clicking on the mini-sheet, not just a single cell.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Inspection level	Total units	Returned result	desired result			1	2	3	S4
2	1	10000	80	80		8	0	2	2	0
3	1	10001	80	125		15	2	3	3	0
4	1	32000	80	125		25	3	5	5	0
5	1	35000	125	125		50	0	8	8	0
6						90	5	13	13	0
7						150	8	20	20	0
8						280	13	32	32	13
9						500	20	50	50	13
10						1200	32	80	80	20
11						3200	50	125	125	32
12						10000	80	200	200	32
13						35000	125	315	315	50
14						150000	200	500	500	80
15						500000	315	800	800	80
16						500001	500	1250	1250	80
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=INDEX($G$2:$J$16,MATCH(B2:B5,$F$2:$F$16,1),MATCH(A2:A5,$G$1:$J$1,0))
Dynamic array formulas.

 

[Fluff]

Thanks for that, how about

=INDEX($G$2:$J$16,XMATCH(B2:B5,$F$2:$F$16,1),MATCH(A2:A5,$G$1:$J$1,0))

 

[Afro_Cookie]

Thanks for that, how about

=INDEX($G$2:$J$16,XMATCH(B2:B5,$F$2:$F$16,1),MATCH(A2:A5,$G$1:$J$1,0))

As always Fluff, you've helped me with a headache in minutes. Appreciate the support

 

[Fluff]

You're welcome & thanks for the feedback.