Unique Vlookup with Static Table Array but Dynamic Cell Values - Problem

[NegativeKurtosis]

Hello Everyone ! Hoping some of the brilliant, creative minds here can offer some suggestions.

I'm working on an assignment and having a great deal of difficulty finding a solution that works. Here is the situation :

I have a dynamic sheet that updates every hour via a web query. I have 2 VBA macros that handle "updating" the columns within the range $I$4:$O$517 with the web query data as it comes in. The TABLE ARRAY RANGE itself DOES NOT CHANGE. I have found a hundred examples on line that explain how to handle dynamic table arrays or dynamic column index numbers but nothing on how to deal with a table array that remains static but has it's values INSIDE that table array changing. The problem is that when the data is changed within the table array $I$4:$O$517 .....ie from the web query update, my Vlookup formula does not work. Nor can I get an index / match or vlookup / If / offset / counta combination formula to work either. I want to have faith that this is not some obscure method for which a creative nested formula can't be created. I have scoured the Forum, Google, as well as manipulating the aformentioned functions a dozen different ways but always end up with an NA error.

Here is my current static formula :

=IF(Q517>0,VLOOKUP(Q517,$I$4:$O$517,7,FALSE),VLOOKUP(Q517,$AB$4:$AH$517,7,FALSE))

I am dealing with positive and negative numbers, hence the >0 reference which checks two different table array ranges and determines which one to obtain the desired value from. I need to be able to obtain the CELL'S VALUE that I get with the static Vlookup formula with a dynamically updating Vlookup formula ( or some derivation of it ).
My lookup value is : Q517 ......this value gets updated every hour, but once this becomes dynamic ( ie....once the web query starts, the vlookup stops working and all other derivations of it). As mentioned previously I have created many combinations of If / Offset / Counta / Index / Match / Vlookup but to no avail.

Any suggestions or insights would be greatly appreciated.

Richard

 

[PaddyD]

Hi - welcome to the board.

You've given a good description of the form of yhour problem but not it's content. Could you take us through a worked example of the problem so we can get a better understanding...

 

[dnorm]

A quick screen shot of both tables (unless the tables have given names) would help me understand what and how your data is "fluctuating" due to the web lookup.

 

[NegativeKurtosis]

PaddyD,

See Screenshots below :

 

[NegativeKurtosis]

Paddy D,

Sorry, I see that the screenshots did not work. Let me figure out how to send a screenshot. I have two annotated screenshots.

Richard

 

[NegativeKurtosis]

Paddy D,

See screenshots and notes below :

<colgroup><col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl68, width: 80"]152.47[/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 86"]0.00429[/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 78"]-0.009335[/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 90"]0.23[/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 76"]2[/TD]
[TD="width: 64"][/TD]
[TD="class: xl68, width: 79"]0.39[/TD]
[TD="width: 64"][/TD]
[TD="class: xl68, width: 85"]195.91[/TD]
[TD="width: 64"][/TD]

[TD="class: xl68"]152.46[/TD]

[TD="class: xl65"]-0.000759[/TD]

[TD="class: xl65"]0.002866[/TD]

[TD="class: xl65"]0.23[/TD]

[TD="class: xl65"]0[/TD]

[TD="class: xl68"]0.00[/TD]

[TD="class: xl68"]195.91[/TD]

[TD="class: xl68"]152.46[/TD]

[TD="class: xl65"]-0.00502[/TD]

[TD="class: xl65"]0.00429[/TD]

[TD="class: xl65"]0.24[/TD]

[TD="class: xl65"]2[/TD]

[TD="class: xl68"]0.39[/TD]

[TD="class: xl68"]196.30[/TD]

[TD="class: xl68"]152.47[/TD]

[TD="class: xl65"]-0.002083[/TD]

[TD="class: xl65"]-0.000759[/TD]

[TD="class: xl65"]0.24[/TD]

[TD="class: xl65"]0[/TD]

[TD="class: xl68"]0.00[/TD]

[TD="class: xl68"]196.30[/TD]

[TD="class: xl68"]152.47[/TD]

[TD="class: xl65"]-0.001614[/TD]

[TD="class: xl65"]-0.00502[/TD]

[TD="class: xl65"]0.24[/TD]

[TD="class: xl65"]2[/TD]

[TD="class: xl68"]0.39[/TD]

[TD="class: xl68"]196.69[/TD]

[TD="class: xl68"]152.46[/TD]

[TD="class: xl65"]0.004546[/TD]

[TD="class: xl65"]-0.002083[/TD]

[TD="class: xl65"]0.24[/TD]

[TD="class: xl65"]0[/TD]

[TD="class: xl68"]0.00[/TD]

[TD="class: xl68"]196.69[/TD]

[TD="class: xl68"]152.46[/TD]

[TD="class: xl65"]-0.015258[/TD]

[TD="class: xl65"]-0.001614[/TD]

[TD="class: xl65"]0.25[/TD]

[TD="class: xl65"]2[/TD]

[TD="class: xl68"]0.39[/TD]

[TD="class: xl68"]197.08[/TD]

[TD="class: xl68"]152.46[/TD]

[TD="class: xl65"]-0.004129[/TD]

[TD="class: xl65"]0.004546[/TD]

[TD="class: xl65"]0.25[/TD]

[TD="class: xl65"]0[/TD]

[TD="class: xl68"]0.00[/TD]

[TD="class: xl68"]197.08[/TD]

[TD="class: xl68"]152.46[/TD]

[TD="class: xl65"]0.003293[/TD]

[TD="class: xl65"]-0.015258[/TD]

[TD="class: xl65"]0.26[/TD]

[TD="class: xl65"]1[/TD]

[TD="class: xl68"]0.19[/TD]

[TD="class: xl68"]197.28[/TD]

[TD="class: xl68"]152.45[/TD]

[TD="class: xl65"]0.008508[/TD]

[TD="class: xl65"]-0.004129[/TD]

[TD="class: xl65"]0.27[/TD]

[TD="class: xl65"]1[/TD]

[TD="class: xl68"]0.19[/TD]

[TD="class: xl68"]197.47[/TD]

[TD="class: xl68"]152.44[/TD]

[TD="class: xl65"]-0.028748[/TD]

[TD="class: xl65"]0.003293[/TD]

[TD="class: xl65"]0.28[/TD]

[TD="class: xl65"]1[/TD]

[TD="class: xl68"]0.19[/TD]

[TD="class: xl68"]197.67[/TD]

[TD="class: xl68"]152.45[/TD]

[TD="class: xl65"]0[/TD]

[TD="class: xl65"]0.008508[/TD]

[TD="class: xl65"]0.29[/TD]

[TD="class: xl65"]1[/TD]

[TD="class: xl68"]0.19[/TD]

[TD="class: xl68"]197.86[/TD]

[TD="class: xl68"]152.45[/TD]

[TD="class: xl65"]-0.067396[/TD]

[TD="class: xl65"]-0.028748[/TD]

[TD="class: xl65"]0.3[/TD]

[TD="class: xl65"]2[/TD]

[TD="class: xl68"]0.39[/TD]

[TD="class: xl68"]198.25[/TD]

[TD="class: xl68"]152.42[/TD]

[TD="class: xl65"]-0.005939[/TD]

[TD="class: xl65"]0[/TD]

[TD="class: xl65"]0.3[/TD]

[TD="class: xl65"]0[/TD]

[TD="class: xl68"]0.00[/TD]

[TD="class: xl68"]198.25[/TD]

[TD="class: xl68"]152.42[/TD]

[TD="class: xl65"]0[/TD]

[TD="class: xl65"]-0.067396[/TD]

[TD="class: xl65"]0.31[/TD]

[TD="class: xl65"]1[/TD]

[TD="class: xl68"]0.19[/TD]

[TD="class: xl68"]198.44[/TD]

[TD="class: xl68"]152.36[/TD]

[TD="class: xl65"]-0.001212[/TD]

[TD="class: xl65"]-0.005939[/TD]

[TD="class: xl65"]0.35[/TD]

[TD="class: xl65"]1[/TD]

[TD="class: xl68"]0.19[/TD]

[TD="class: xl68"]198.64[/TD]

[TD="class: xl68"]152.35[/TD]

[TD="class: xl65"]0[/TD]

[TD="class: xl65"]0[/TD]

[TD="class: xl65"]0.38[/TD]

[TD="class: xl65"]1[/TD]

[TD="class: xl68"]0.19[/TD]

[TD="class: xl68"]198.83[/TD]

[TD="class: xl68"]152.35[/TD]

[TD="class: xl65"]#REF![/TD]

[TD="class: xl65"]0.073335[/TD]

[TD="class: xl65"]1.476824[/TD]

[TD="class: xl65"]1[/TD]

[TD="class: xl68"]0.19[/TD]

[TD="class: xl68"]199.03[/TD]

[TD="class: xl68"]152.35[/TD]

[TD="class: xl65"]#REF![/TD]

[TD="class: xl65"]0.014227[/TD]

[TD="class: xl65"]6.922212[/TD]

[TD="class: xl65"]1[/TD]

[TD="class: xl68"]0.19[/TD]

[TD="class: xl68"]199.22[/TD]

[TD="class: xl68"]152.35[/TD]

[TD="class: xl66"]ELECTRICAL[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]DIFFERENCE[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]BIN LIMITS[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]CUMULATIVE[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]VALUES[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]DIFFERENCES[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]VALUES[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]FOR POSITIVE[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]FREQUENCY[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]PERCENT[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]PERCENT[/TD]
[TD="class: xl67"] [/TD]

[TD="class: xl66"]ONLY[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]ONLY[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]DIFFERENCES[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]FREQUENCY[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl66"]FREQUENCY[/TD]
[TD="class: xl67"]
[/TD]

</tbody>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Imported Hourly

Phase Angle

Calculation

<colgroup><col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:5705;width:117pt" width="156"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="width:48pt" span="5" width="64"> </colgroup><tbody>
[TD="class: xl71, width: 85"]195.91[/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 68"]-5.783861[/TD]
[TD="class: xl65, width: 64"]-0.0112[/TD]
[TD="class: xl65, width: 64"]-0.00402[/TD]
[TD="width: 156"][/TD]
[TD="width: 70"][/TD]
[TD="width: 86"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: center"]#N/A[/TD]
[TD="width: 64, align: center"]#N/A[/TD]
[TD="width: 64, align: center"]#N/A[/TD]
[TD="width: 64"][/TD]

[TD="class: xl71"]195.91[/TD]

[TD="class: xl65"]-0.006906[/TD]
[TD="class: xl65"]0.000268[/TD]
[TD="class: xl65"]-0.00218[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]196.30[/TD]

[TD="class: xl65"]-0.000491[/TD]
[TD="class: xl65"]-0.00294[/TD]
[TD="class: xl65"]0.006397[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]196.30[/TD]

[TD="class: xl65"]-0.007958[/TD]
[TD="class: xl65"]0.001377[/TD]
[TD="class: xl65"]-0.00149[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]196.69[/TD]

[TD="class: xl65"]-0.000706[/TD]
[TD="class: xl65"]-0.00357[/TD]
[TD="class: xl65"]-0.00786[/TD]

[TD="align: right"]144.1634[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]196.69[/TD]

[TD="class: xl65"]-0.005186[/TD]
[TD="class: xl65"]-0.00948[/TD]
[TD="class: xl65"]-0.00872[/TD]

[TD="align: right"]143.1907[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]197.08[/TD]

[TD="class: xl65"]-0.00493[/TD]
[TD="class: xl65"]-0.00417[/TD]
[TD="class: xl65"]0.000849[/TD]

[TD="align: right"]143.3852[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]197.08[/TD]

[TD="class: xl65"]-0.019429[/TD]
[TD="class: xl65"]-0.01441[/TD]
[TD="class: xl65"]-0.01233[/TD]

[TD="align: right"]137.9377[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]197.28[/TD]

[TD="class: xl65"]-0.018538[/TD]
[TD="class: xl65"]-0.01646[/TD]
[TD="class: xl65"]-0.01484[/TD]

[TD="align: right"]137.9377[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]197.47[/TD]

[TD="class: xl65"]-0.013162[/TD]
[TD="class: xl65"]-0.01155[/TD]
[TD="class: xl65"]-0.01609[/TD]

[TD="align: right"]138.1323[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]197.67[/TD]

[TD="class: xl65"]-0.00304[/TD]
[TD="class: xl65"]-0.00759[/TD]
[TD="class: xl65"]0.007672[/TD]

[TD="align: right"]143.5798[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]149.8054[/TD]

[TD="class: xl71"]197.86[/TD]

[TD="class: xl65"]-0.036334[/TD]
[TD="class: xl65"]-0.02108[/TD]
[TD="class: xl65"]-0.01695[/TD]

[TD="align: right"]130.5447[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]198.25[/TD]

[TD="class: xl65"]-0.021076[/TD]
[TD="class: xl65"]-0.01695[/TD]
[TD="class: xl65"]-0.02024[/TD]

[TD="align: right"]133.463[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]198.25[/TD]

[TD="class: xl65"]-0.084343[/TD]
[TD="class: xl65"]-0.08764[/TD]
[TD="class: xl65"]-0.09614[/TD]

[TD="align: right"]118.677[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]198.44[/TD]

[TD="class: xl65"]-0.093575[/TD]
[TD="class: xl65"]-0.10208[/TD]
[TD="class: xl65"]-0.07333[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]198.64[/TD]

[TD="class: xl65"]-0.102083[/TD]
[TD="class: xl65"]-0.07333[/TD]
[TD="class: xl65"]-0.07333[/TD]

[TD="align: right"]115.1751[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]198.83[/TD]

[TD="class: xl65"]-0.074547[/TD]
[TD="class: xl65"]-0.07455[/TD]
[TD="class: xl65"]-0.00715[/TD]

[TD="align: right"]152.34658[/TD]
[TD="align: right"]0.006564[/TD]

[TD="align: right"]120.0389[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]199.03[/TD]

[TD="class: xl65"]-0.074547[/TD]
[TD="class: xl65"]-0.00715[/TD]
[TD="class: xl65"]-0.00121[/TD]

[TD="align: right"]120.0389[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"]199.22[/TD]

[TD="class: xl71"]-152.36[/TD]
[TD="class: xl65"]-152.352[/TD]
[TD="class: xl65"]-152.352[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl71"][/TD]
[TD="class: xl71"][/TD]
[TD="class: xl71"][/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: right"]56.22568[/TD]
[TD="align: right"]232[/TD]

[TD="class: xl66"]CUMULATIVE[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"]3RD[/TD]
[TD="class: xl69"]2ND[/TD]
[TD="class: xl69"]1ST[/TD]
[TD="class: xl69"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]3RD[/TD]
[TD="class: xl66"]2ND[/TD]
[TD="class: xl66"]1ST[/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66"]PERCENT[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67, colspan: 4"]THREE PHASE LAG VALUE DIFFERENCES[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69, colspan: 5"]CORRESPONDING PROBABILITY PERCENTAGES[/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl66"]FREQUENCY[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl70, colspan: 5"]FOR PHASE ANGLE TO RETAIN CURRENT VOLTAGE[/TD]
[TD="class: xl68"]
[/TD]

</tbody>

The imported hourly phase angle calculation is dynamic, it updates every hour from a web and it is what "feeds" the rest of the calculations...ie...the columns A through I. Columns Q,R,S are updated by the data calculated in columns A through I. This formula :

=IF($Q$517>0,VLOOKUP($Q$517,$I$4:$O$517,7,TRUE),VLOOKUP($Q$517,$AB$4:$AH$517,7,TRUE))

=IF($R$517>0,VLOOKUP($R$517,$I$4:$O$517,7,TRUE),VLOOKUP($R$517,$AB$4:$AH$517,7,TRUE))

=IF($S$517>0,VLOOKUP($S$517,$I$4:$O$517,7,TRUE),VLOOKUP($S$517,$AB$4:$AH$517,7,TRUE))

Is then supposed to find the closest corresponding values ( $Q$517, $ R$517, $S$517 in the table array : $I$4:$O$517 or table array : $AB$4:$AH$517 depending on whether or not the value is greater than 0. That value ( also called...the percentage probability of the phase angle ) is in column 7 as noted in the formula. The problem is that columns X,Y,Z do not update dynamically when all the other columns change as the imported hourly phase angle calculation provides new data. The table array doesn't change in size but it does inherit new cell values.

To recap, columns A through S have the potential to change every hour. Columns X,Y,Z need to be able to update as well based on the changes in the appropriately chosen table array. Hope this helps clarify.

Richard

 

[dnorm]

Do your tables have names?

you could try switching to table names:-

=IF($S$517>0,VLOOKUP($S$517,Table1[#Data],7,TRUE),VLOOKUP($S$517,Table2[#Data],7,TRUE))

The same could be said for the web updated data if you can place that in a dynamic table as long as the name stays the same - you could replace Q, R &S so:-

=IF(UpdateQ[#Data]>0,VLOOKUP(UpdateQ[#Data],Table_I_O[#Data],7,TRUE),VLOOKUP(UpdateQ[#Data],Table_AB_AH[#Data],7,TRUE))

You might have to change the column you are looking in to allow for a complete table, with the [#Data] link it shouldn't matter where the match is within it.

From you screen shots it is hard to discern where AB:AH is, so I hope this helps.

 

[NegativeKurtosis]

dnorm,

Table array AB:AH is off to the right; Thanks for the suggestion ! I will investigate renaming the table names.

Thanks,
Richard

 

[dnorm]

dnorm,

Table array AB:AH is off to the right; Thanks for the suggestion ! I will investigate renaming the table names.

Thanks,
Richard

How have you got on? Did that work for you?

 

[NegativeKurtosis]

dnorm,

Thanks for checking back in. Unfortunately it did not work; the reason being because it is a multi - cell array formula and Excel 2016 will not allow multi - cell array formulas to be used in tables. I am still working on it. Here is what I have created in the interim :

=IF(Q516>0,INDEX($O$4:$O$517,MATCH($Q$516,$I$4:$I$517,1)))

AND

=IF(Q516<0,INDEX($AB$4:$AB$517,MATCH($Q$516,$AH$4:$AH$517,1)))

Been testing this today; not 100 % certain that this is working either. Beginning to think that I can't do this analysis in Excel. I don't want to, but I may have to try to solve this in R Studio unless someone in the forum has any viable suggestions.

Richard