Dynamically changing array elements in a formula

[Bumba]

How can I create an array in a formula which while filling down the items of the array change in a specific manner.


What I want is in cell B15, while in a function like say dget where there is an array field part in the formula, the contents of the array will be A14 and A15 i.e. {"Customer ID";"12D101"}, then when I fill down the formula the contents of the array in cell B16 will be A14 and A16 i.e. {"Customer ID";"12D102"}, then in cell B17 will be A14 and A17 i.e. {"Customer ID";"12D103"} and so on..


How can I do this?


NOTE: I know the results in the sample file can be achieved by vlookup or index+match combo or any other method and that is not the issue, all I want to know is how do I change the array elements while I'm filling down the cells in cells B16 to B18.

I've tried to use the below code

=CONCATENATE("{",CHAR(34),INDIRECT("$a$14"),CHAR(34),";",CHAR(34),A15,CHAR(34),"}")

instead of A14:A15 in my formula but it gives #VALUE ! error.

 

[RoryA]

Welcome to the forum.

You can't do that as it just won't work with a D-function, which requires a contiguous range (not an array) for the criteria.