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

 

[NegativeKurtosis]

dnorm,

Do you know if it is possible to combine / compare these two arrays ? If so, how ?

=IF(S516>0, INDEX($O$4:$O$517,MATCH($S$516,$I$4:$I$517,1))) / =IF(S516<0, INDEX($AH$4:$AH$517,MATCH($S$516,$AB$4:$AB$517,1)))

So that depending on whether or not S516 is greater than or less than zero the results will go to the appropriate column / row array.....ie...$I$4:$I$517 ( for greater than zero ) or to $AB$4:$AB$517 ( for less than zero ) ?

Richard

 

[dnorm]

Sorry for the very late reply, I didn't get an update about your last comment/ request.

Ill look into it and get back to you.

Could you email me the file dnorm_i@hotmail.com