dynamically identify result vector? 1

[afbutlerandassoc]

can it be done? I can't be entering 5000 result vectors every time the selection changes - is there a way to identify it using this sort of formula?

MATCH('Retail routing'!D3,'LCR RATES'!(CONCATENATE("A",MATCH(A5,ratename))))CONCATENATE("u",MATCH(A5,ratename))))))))),0)-1

The result I am aiming for is the value of the cell found in array a58:u58

can something like this be done and how?

 

[DaleWatson123321]

This was somewhat of a challenge. I tried various options, and finally came up with the following working formula.

=IF(I5="","",VLOOKUP(A5,vr,CHOOSE(FIND(I5,VLOOKUP(A5,vr,5,FALSE)&VLOOKUP(A5,vr,7,FALSE)&VLOOKUP(A5,vr,9,FALSE)&VLOOKUP(A5,vr,11,FALSE)&VLOOKUP(A5,vr,13,FALSE)&VLOOKUP(A5,vr,15,FALSE)&VLOOKUP(A5,vr,17,FALSE)&VLOOKUP(A5,vr,19,FALSE)&VLOOKUP(A5,vr,21,FALSE)),4,4,4,6,6,6,8,8,8,10,10,10,12,12,12,14,14,14,16,16,16,18,18,18,20,20,20),FALSE))

The formula looks up the text in column A, and concatenates the codes from the 9 different fields.

The FIND function references the code in the adjacent cell (I5) and is used to locate the position of this code within the concatenated text.

The CHOOSE arguments (positions) reference each of the fields containing the matching VALUE of each code.

Therefore, regardless of which field a code is located, the formula will return the value for that code.

I'm pleased that this solution resolved your rather unique task.

Best Regards, ...Dale Watson dwatson@bsi.gov.mb.ca