Need help to search two-column array for a non-numerical match + return corresponding value from another column

[kt13968]

Hello,

I am looking for a formula which finds the previous cell which matches C(n), e.g. C12, if I search UP both columns of the array C2:D(n-1), e.g. C2:D11.

If the first match found is in column C, I would like the formula to return the corresponding value in column M. In the example, C2 is the previous match for C12, so the formula should return M2 (1.26).

But if the first match is in column D, I would like it to return the corresponding value from column P.

I’ve tried reading the instructions for the index, match, and lookup functions but none of them have helped me get any closer to what I am trying to do I’m afraid. I’d really appreciate the help.

Example.ods (27.5 KB)

[Villeroy]

Formula in Q12: =INDEX($P$2:$P11;MATCH($C12;$C$2:$C11;0))

[kt13968]

Thanks for the quick reply. This is similar to where I got to with it; it only does part of what I had in mind. I.e

• it only searches column c for a match, whereas ideally it would search both columns c and d and stop at the first match found in either one of them
• it only returns a value from column P, but if the most recent match is found in column c it should return the value from column m; if column d, then it should return result from column p

[Villeroy]

Well, this is not a database. It’s just a spreadsheet, an arithmetic calculator.
Formula in Q12: =INDEX($P$2:$P11;MATCH($C12&"|"&$D12;$C$2:$C11&"|"&$D$2:$D11;0))

[koyotak]

Example_Resultsolutions-v0001.ods (23.7 KB)
learning by doing