I am not sure where can I ask my question, but after a small research I decided to ask here:
I have a question regarding the following table:
In Input A — Cell B4 we put a positive number and in Input B — Cell B7 we put also a positive number. Regarding the pregiven table Value A, Value B and Value C — Array D3:I9 with all of the so depicted values (these values can be random, in my case I just used the prime numbers), I need a way to generate an output in Output C — K6.
The relations to produce a value for the Output is the following: The number in Input A — Cell B4 is related to Value A — Array D3:D7 and the number in Input B — Cell B7 is related to Value B — Array E3:I7. The combination between the Value A and Value B is related with Value C — Array E9:I9.
(1) The Input A generates Value A by picking from Array D3:D7 the smallest number bigger than the value in B4.
(2) The Input B can only be consequent to Input A. When Value A is generated, Value B is on the same row as Value A, at the same time Value B is the biggest number in Array E[ROW_VALUE_A]:I[ROW_VALUE_A].
(3) The Output C can only be generated after we generate the Value B. The Value C is the only number that is found on the same column as Value B.
(4) Output C reads Value C.
Since this is a little complicated to explain, I feel like giving an example will make things smoother:
(1) If we put number 2,6 in B4, the Value A has to go Cell D4 — 3, because 3 is the smallest number in Array D3:D7. Stage 1
(2) This therefore leads us to narrowing down our array to pick Value B from Array E4:I4. In this array the smallest value bigger than Input B — B7 is 79 — Cell H4. 79 — Cell H4 will be our Value B. Stage 2
(3) Since 79 is in Cell H4, the only number from the column, that goes in Values C — Array E9:I9 is 139 — H9. Stage 3
(4) Obviously we print H9 to K6. Stage 4
If somebody asked me how to achieve this, I will not be very encouraged, so I will show you my progress so far:
(1) I managed to pick the smallest value from array Value A — D3:D7, bigger than Input A — B4. The formula is the following:
Which has to be applied with ctrl+shift+enter, as it is an array formula.
Regarding (2) and (3) I don't know how to force the above formula over the array determined by the row of the Value A.
If I manage to read the coordinates of the cell to the Value A — Array D3:D7, this problem is probably solved.
Here I apply the file, so if anybody is interested, could give it a try: The Exel File
I prefer not to do it with coding in Macros, but with an "in-cell" formula typed in Output C — K6.
Thank you for your attention and good luck! :)