I am working on a spreadsheet in which I need to first find a value in a running sum that is the greatest value less than a criterion value. for this I have successfully used both lookup and match functions each alone. however, I then need to use not only the cell value that is found, but also the value of two cells relative to that but going both directions, one in the same column, one in a different column and different row.
so, to clarify, if I have the following data:
A1: 2012 B1: 2013 C1: 2014 D1: 2015
A2: 3.4 B2: 4.1 C2: 4.8 D2: 5.2
A3: 5 (criteria goal)
my intention is to ultimately have a function that uses cells C2, C1 and D1
the formula below returns the value of C2 correctly
=LOOKUP(A3,A2:D2)
but I cannot figure out how to write a function that then pulls C1 and D1 values as well, using those along with C2
Bookmarks