Dynamic Array and VLOOK up error

KevinMcC

New Member
Joined
May 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1
2InputCumalative TotalPercentage12203.6%2204.3%2204.8%2205.9%2207.7%948Apples
31Peaches2563.6%24607.1%4608.7%4609.5%46011.8%46015.4%411Necterines
42Strawberries4547.1%341007.1%41008.7%41009.5%410011.8%410015.4%732Pears
53Kiwi4507.1%441407.1%41418.7%01000.0%01000.0%01000.0%832Bananas
64Necterines4467.1%5822014.3%822017.4%818019.0%818023.5%818130.8%514Cherries
75Cherries84214.3%6830014.3%830017.4%826019.0%826023.5%826030.8%
86Grapes83414.3%7838014.3%838017.4%834119.0%02600.0%02600.0%
97Pears82614.3%8846014.3%846017.4%842019.0%834123.5%02600.0%
108Bananas81814.3%91056117.9%04600.0%04200.0%03400.0%02600.0%
119Apples/Oranges101017.9%5646423426
121056Not Zero48Not Zero11Not Zero32Not Zero32Not Zero14
13How Many Apples and OrangesRandom48Random11Random32Random32Random14
14Data from another Sheet69Exclusion9Exclusion4Exclusion7Exclusion8Exclusion5
15First Fruit
167597OrangesOranges1VLOOKUP
17Apples1#N/A
18Necterines6#N/A
19Pears3
20Bananas5
21Cherries5
22
23
Sheet1
Cell Formulas
RangeFormula
I2:I10,AC2:AC10,X2:X10,S2:S10,N2:N10I2=I1+H2
J2:J10,AD2:AD10,Y2:Y10,T2:T10,O2:O10J2=IF(AND(I1<J$13,I2>=J$13),1,0)
K2:K10K2=H2/$H$11
P2:P10P2=M2/$M$11
U2:U10U2=R2/$R$11
Z2:Z10Z2=W2/$W$11
AE2:AE10AE2=AB2/$AB$11
AG2AG2=J14
AH2AH2=J13
AI2:AI6AI2=IF(AND(AG2=9,$D$16="Apples"),"Oranges",IF(AND(AG2=9,$D$16="Oranges"),"Apples",VLOOKUP(AG2,$A$3:$B$11,2)))
AG3AG3=O14
AH3AH3=O13
AG4AG4=T14
AH4AH4=T13
AG5AG5=Y14
AH5AH5=Y13
AG6AG6=AD14
AH6AH6=AD13
D3:D10D3=D4+C3
E3:E11E3=C3/$C$12
D11D11=C11
H2:H10H2=C3
H11,C12,AB11,W11,R11,M11H11=SUM(H2:H10)
M2:M10,AB2:AB10,W2:W10,R2:R10M2=H2-J2*H2
J12,AD12,Y12,T12,O12J12=IF(J13=0,1,J13)
J13,AD13,Y13,T13,O13J13=RANDBETWEEN(I2,I10)
J14,AD14,Y14,T14,O14J14=INDEX($G$2:$G$10,MATCH(1,J2:J10,0))
C16C16=RANDBETWEEN(1,100)
D16D16=IF(B16>C16,"Apples","Oranges")
L17L17=IF(COUNTIF(G16:G21,"Apples")<1,0,VLOOKUP("Apples",G16:I21,3))
L18L18=VLOOKUP("Apples",G16:I21,3)
G16G16=IF(B16>C16,"Apples","Oranges")
G17:G21G17=IF(($C$14-(COUNTA(G$16:$G16))>0),AI2,"")
I16:I21I16=IF(G16="Apples",RANDBETWEEN(1,9),RANDBETWEEN(1,6))
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,284
Messages
5,705,493
Members
421,398
Latest member
Rahat Anwar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top