Q:

Dyanamic range and criteria for a VLOOKUP

I'm trying to create a VLOOKUP which can utilise COLUMN number for both the criteria and range start position whilst getting the index number from COLUMNS count.
I have a spreadsheet that I import a data set into for analysis. I add a couple of rows at the top and use one of those rows to mark specific columns as containing values which are fixed - as in, the values come from a predefined selection like order_status would have fixed values of created, submitted, fulfilled, paid, etc.
As I mark each as fixed, the row above incrementally assigns a number value to each of the columns. If I have 16 fixed values, there will be columns which increment from 1 to 16.
In an analysis sheet, I have 16 columns that I use to perform some analysis. The first thing it does for every row in the data set is assign a unique identifier for every unique value in the data set column or, if the value has previously existed in rows before it, lookup the number that was assigned to it last time. I can then later lookup the distinct values based upon those unique numbers and associate a count of them to display how frequently each of those values exist in a data set.
I have created this analysis sheet in a way where it already knows the positions of the columns. Example shown below:
=IF(B5="",0,IF(COUNTIF(B$3:B4,B5)>0,VLOOKUP(B5,B:AB,27,FALSE),MAX(AB$3:AB4)+1))

My question is how to make the criteria and start position of the formula dynamic so that I can change which columns I categorise as fixed without having to adjust the analysis formulas.
So, for eample above, column B is a fixed value column. I already have the following formula:
formula=MATCH(AB2,$A2:$Z2,0)

which tells me that column 2 (otherwise known as B) is the first fixed value.
If I reproduced the formula to work dynamically using what I know, it would be:
=IF(COUNTIF($column(formula)+row (=3): column(formula)+ current_row(5)-1
           , column(formula)+ current_row(5)>0
               , VLOOKUP (column(formula)+current_row(5), column(formula):AB
               , COLUMNS(column(formula):AB)
               , false)
           , MAX(AB$3:AB4)+1))

However, the way I've done this formula doesn't work - I don't know how to explicitly set the row numbers in the formula. I hope I have provided enough information to explain what I'm trying to do, thanks in advance.
Here's a picture of what it looks like - I've had to blur certain info for sensitivity reasons. Thanks.

A:

You can use the INDIRECT function to convert generated strings into range references.
=COUNTIF(INDIRECT(*formula to generate range string*),INDIRECT(*formula to generate cell string*))
However, INDIRECT is a volatile function that will recalculate every time you make any change to the workbook. If you have an entire column of them, your workbook could slow down heavily, especially if combining them with exact lookups.
One alternative option is using the CHOOSE function, which can be used if you're choosing from known fixed possible ranges:
CHOOSE(*rangenumber*,*range1*,*range2*,*range3*,...)
Choose is quite good in that it is not a volatile function.
If the number or size of ranges aren't fixed, you might instead be able to use the OFFSET function:
OFFSET(*starting point*,*row offset*,*column offset*,*number of rows*, *number of columns*)
This lets you start from an anchor point, and then calculate the distance to your desired range and also the size of the desired range.
OFFSET is also volatile, however you can save it as a defined name and then reuse that defined name in a column of formulas, requiring it to only be calculated once before being used in all formulas, rather than having to be calculated in every formula.

