HI,
I think the solution might be easy but I am stuck.
My data is in column A10 + B10 & A12 + B12.
There is a whole series of amounts in column A.
In column B is the amount when single.
In column C is the amount when not single.
I am looking to find B10 (1,945) in column A.
Obviously, that won't be an exact match.
Therefore, I am looking for the amount between 2 rows.
It needs to return the amount in column B when single or column C when not single.
And it needs to be the amount in the row that is less or equal to 1,945 but higher than the amount in the row above.
1950 (cell A 5) is less or equal than 1945;
1935 (cell A 4) is higher than 1945
Therefore, I need the amount 307.13 in column B5.
Hopefully someone is able to set me up the right path.
Thank you very much in advance.
I think the solution might be easy but I am stuck.
My data is in column A10 + B10 & A12 + B12.
There is a whole series of amounts in column A.
In column B is the amount when single.
In column C is the amount when not single.
I am looking to find B10 (1,945) in column A.
Obviously, that won't be an exact match.
Therefore, I am looking for the amount between 2 rows.
It needs to return the amount in column B when single or column C when not single.
And it needs to be the amount in the row that is less or equal to 1,945 but higher than the amount in the row above.
1950 (cell A 5) is less or equal than 1945;
1935 (cell A 4) is higher than 1945
Therefore, I need the amount 307.13 in column B5.
Hopefully someone is able to set me up the right path.
Thank you very much in advance.
1 | A | B | C | |||||||||||
2 | Single | Not single | ||||||||||||
3 | 1,920.00 | 294.29 | 51.71 | |||||||||||
4 | 1,935.00 | 300.71 | 55.73 | |||||||||||
5 | 1,950.00 | 307.13 | 59.74 | |||||||||||
6 | 1,965.00 | 313.55 | 63.75 | |||||||||||
7 | 1,980.00 | 319.97 | 68.27 | |||||||||||
8 | ||||||||||||||
9 | Looking for the following result | |||||||||||||
10 | Single | 1,945.00 | needs to return 307.13 | |||||||||||
11 | ||||||||||||||
12 | Not single | 1,934.00 | needs to return 55.73 | |||||||||||
lookup 1,945.00 | in column A | |||||||||||||
if less or equal than amount found in A5 but more than amount in A4 then return amount in cell b5 on condition that A10 = single | ||||||||||||||
lookup 1,934.00 | in column A | |||||||||||||
if less or equal than amount found in A4 but more than amount in A3then return amount in cell C4 on condittion that A12 = C3 [not single] |