'Retrieve value of cell B assume that cell A has certain value (which A doesn't)
I have a cell A with some base value. Cell B is a calculated function of A and some other variable (a complex chained one).
I want to set value of colum C like this:
C1 = value of B when A = 100
C2 = value of B when A = 101
C3 = value of B when A = 102
Assume that B is the result of complex chained calculation and it's not possible to copy the calculation from B into C.
Solution 1:[1]
Some Tricky ROW-INDEX-MATCH Combination
The formula is written to start in C$1 and looking in column A and B. It is fairly obvious how to adjust to fit your needs. It might be better to choose a smaller range for efficiency i.e. instead of A:A to use A$1:A$1000 and the same for the B column.
=IF(ISNUMBER(MATCH(100+ROW()-ROW(C$1)+1,A:A,0)),INDEX(B:B,MATCH(100+ROW()-ROW(A$1)+1,A:A,0)),"")
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | VBasic2008 |