Dynamic Array and VLOOK up error

[KevinMcC]

Hi,
I'm struggling with a VLOOK up on a Dynamic Array. The Sample I am including is not using a variable for the Array as that data comes from another sheet.

The basic setup is I have 10 different fruit. Either Apples or Oranges have to be chosen as the first fruit. It is random if the Apple/Orange not chosen is selected later. This I have working. Each fruit then has a random number given it (1-6) but apples can have (1-9) I need to do a calculation with the random numbers assigned to Apples and Oranges. This is where I have my VLOOKUP Formula and the problem starts.

I have Two VLOOKUPs here the second is =VLOOKUP("Apples",G16:I21,3) and this is part of the first formula as well. The first formula is meant to return "0" in the case where Apples are not one of the fruits selected.
=IF(COUNTIF(G16:G21,"Apples")<1,0,VLOOKUP("Apples",G16:I21,3)) In both cases if the first fruit or the third fruit is "Apples" then I get a result. All other locations return #N/A. I figure there is something simple I am overlooking as I don't think of myself and very knowledgeable in Excel. Most of what I have here I got by looking up solutions in these forums.

Kevin

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1
2			Input	Cumalative Total	Percentage		1	2	2	0	3.6%		2	2	0	4.3%		2	2	0	4.8%		2	2	0	5.9%		2	2	0	7.7%		9	48	Apples
3	1	Peaches	2	56	3.6%		2	4	6	0	7.1%		4	6	0	8.7%		4	6	0	9.5%		4	6	0	11.8%		4	6	0	15.4%		4	11	Necterines
4	2	Strawberries	4	54	7.1%		3	4	10	0	7.1%		4	10	0	8.7%		4	10	0	9.5%		4	10	0	11.8%		4	10	0	15.4%		7	32	Pears
5	3	Kiwi	4	50	7.1%		4	4	14	0	7.1%		4	14	1	8.7%		0	10	0	0.0%		0	10	0	0.0%		0	10	0	0.0%		8	32	Bananas
6	4	Necterines	4	46	7.1%		5	8	22	0	14.3%		8	22	0	17.4%		8	18	0	19.0%		8	18	0	23.5%		8	18	1	30.8%		5	14	Cherries
7	5	Cherries	8	42	14.3%		6	8	30	0	14.3%		8	30	0	17.4%		8	26	0	19.0%		8	26	0	23.5%		8	26	0	30.8%
8	6	Grapes	8	34	14.3%		7	8	38	0	14.3%		8	38	0	17.4%		8	34	1	19.0%		0	26	0	0.0%		0	26	0	0.0%
9	7	Pears	8	26	14.3%		8	8	46	0	14.3%		8	46	0	17.4%		8	42	0	19.0%		8	34	1	23.5%		0	26	0	0.0%
10	8	Bananas	8	18	14.3%		9	10	56	1	17.9%		0	46	0	0.0%		0	42	0	0.0%		0	34	0	0.0%		0	26	0	0.0%
11	9	Apples/Oranges	10	10	17.9%			56					46					42					34					26
12	10		56					Not Zero		48			Not Zero		11			Not Zero		32			Not Zero		32			Not Zero		14
13			How Many		Apples and Oranges			Random		48			Random		11			Random		32			Random		32			Random		14
14	Data from another Sheet		6		9			Exclusion		9			Exclusion		4			Exclusion		7			Exclusion		8			Exclusion		5
15		First Fruit
16		75	97	Oranges			Oranges		1			VLOOKUP
17							Apples		1			#N/A
18							Necterines		6			#N/A
19							Pears		3
20							Bananas		5
21							Cherries		5
22
23
Sheet1

Cell Formulas
Range	Formula
I2:I10,AC2:AC10,X2:X10,S2:S10,N2:N10	I2	=I1+H2
J2:J10,AD2:AD10,Y2:Y10,T2:T10,O2:O10	J2	=IF(AND(I1<J$13,I2>=J$13),1,0)
K2:K10	K2	=H2/$H$11
P2:P10	P2	=M2/$M$11
U2:U10	U2	=R2/$R$11
Z2:Z10	Z2	=W2/$W$11
AE2:AE10	AE2	=AB2/$AB$11
AG2	AG2	=J14
AH2	AH2	=J13
AI2:AI6	AI2	=IF(AND(AG2=9,$D$16="Apples"),"Oranges",IF(AND(AG2=9,$D$16="Oranges"),"Apples",VLOOKUP(AG2,$A$3:$B$11,2)))
AG3	AG3	=O14
AH3	AH3	=O13
AG4	AG4	=T14
AH4	AH4	=T13
AG5	AG5	=Y14
AH5	AH5	=Y13
AG6	AG6	=AD14
AH6	AH6	=AD13
D3:D10	D3	=D4+C3
E3:E11	E3	=C3/$C$12
D11	D11	=C11
H2:H10	H2	=C3
H11,C12,AB11,W11,R11,M11	H11	=SUM(H2:H10)
M2:M10,AB2:AB10,W2:W10,R2:R10	M2	=H2-J2*H2
J12,AD12,Y12,T12,O12	J12	=IF(J13=0,1,J13)
J13,AD13,Y13,T13,O13	J13	=RANDBETWEEN(I2,I10)
J14,AD14,Y14,T14,O14	J14	=INDEX($G$2:$G$10,MATCH(1,J2:J10,0))
C16	C16	=RANDBETWEEN(1,100)
D16	D16	=IF(B16>C16,"Apples","Oranges")
L17	L17	=IF(COUNTIF(G16:G21,"Apples")<1,0,VLOOKUP("Apples",G16:I21,3))
L18	L18	=VLOOKUP("Apples",G16:I21,3)
G16	G16	=IF(B16>C16,"Apples","Oranges")
G17:G21	G17	=IF(($C$14-(COUNTA(G$16:$G16))>0),AI2,"")
I16:I21	I16	=IF(G16="Apples",RANDBETWEEN(1,9),RANDBETWEEN(1,6))

 

[StephenCrump]

The problem is in your VLOOKUP. G16:G21 is not sorted, so you need an exact match:

L17: =IF(COUNTIF(G16:G21,"Apples")<1,0,VLOOKUP("Apples",G16:I21,3,))

Or =IFERROR(VLOOKUP("Apples",G16:I21,3,),0)

 

[KevinMcC]

Thank you, I have been pulling my hair out over this. As I suspected it was something simple I was overlooking. and not being that knowledgeable, I could not see my error.
Thank you again!
Kevin

 

[StephenCrump]

Ha! I still occasionally make this same mistake, even though I rarely use anything other than exact match.

A little knowledge just makes it faster to recognise and fix the silly error.