 # Return Row value in a 2D array with column and value input

• Hi,
I have a column and data input value and need to determine the row output.

Table:
_______|40 60 80 100
(1/2)___|4 3 2 2
(3/4)___|8 6 6 6
(3/4)___|10 8 6 6
(2-1/2)_|16 13 12 12

The column and row indicies are determined as follows:
If column input value = column value then column index = column number
If column input value > column value then column index = next column number
If column input value > max column value the column index = max column number

If data input value = data value then row index = index of data value in column index
If data input value > data value then row index = next index of data value in column index
If data input value > max data value then row index = max index of data value

Please note that the data values and row output are not unique.

Here is some sample input and how the output is determined from the table:
Column Input-->actual | Data Input-->actual | Row Output
40-->40 | 4-->4 | (1/2)
41-->60 | 4-->6 | (3/4)
65-->80 | 3-->6 | (3/4)
80-->80 | 2-->2 | (1/2)
81-->100 | 2-->2 | (1/2)
85-->100 | 7-->12 | (2-1/2)

I hope my description of the problem is sufficient. I have been struggling with this for a week so any help would be greatly appreciated. Excel functions would be nice but VBA code would also work. Thanks.

• Re: Return Row value in a 2D array with column and value input

Herbds7,
Thank you very much for the prompt response. I have incorporated the solution you provided and it works for the most part. Determining the actual data value works great. I modified the "data actual" formula. This seems to work better since the delta between columns is not consistent. However, the row output gives me sporadic values it seems. I suspect its because the data values are not in a consecutive order column-wize (look at the values before and after 70 in the 150 column). I think the solution you provided would work perfectly if not for this. I have attached a spreadsheet with an actual table and sample input/output if you can have a second look. Thanks.

## Files

• Re: Return Row value in a 2D array with column and value input

Thanks Herbds7!