look for number between 2 rows - return number from adjacent column

[Vcoppens]

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.

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]

 

[kevin9999]

Book1
	A	B	C	D
3		Single	Not single
4	1,920.00	294.29	51.71
5	1,935.00	300.71	55.73
6	1,950.00	307.13	59.74
7	1,965.00	313.55	63.75
8	1,980.00	319.97	68.27
9
10	Single		1,945.00	307.13
11
12	Not Single		1,934.00	55.73
Sheet1

Cell Formulas
Range	Formula
D10	D10	=INDEX($B$4:$B$8,MATCH(MIN(IF($A$4:$A$8>=C10,$A$4:$A$8)),$A$4:$A$8,0))
D12	D12	=INDEX($C$4:$C$8,MATCH(MIN(IF($A$4:$A$8>=C12,$A$4:$A$8)),$A$4:$A$8,0))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

If you have xl365 another option

Fluff.xlsm
	A	B	C	D
1		Single	Not single
2	1920	294.29	51.71
3	1935	300.71	55.73
4	1950	307.13	59.74
5	1965	313.55	63.75
6	1980	319.97	68.27
7
8	Single		1945	307.13
9	Not Single		1934	55.73
Main

Cell Formulas
Range	Formula
D8:D9	D8	=INDEX($B$2:$C$6,XMATCH(C8,$A$2:$A$6,1),MATCH(A8,$B$1:$C$1,0))

 

[Vcoppens]

Thank you so very much!
It worked exactly how I wanted it.

Thank you again.
Have a wonderful day/evening.

 

[Peter_SSs]

Please update your details as requested above

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sounds like you have a recent version though so you could also consider this

22 10 28.xlsm
	A	B	C	D
1		Single	Not single
2	1920	294.29	51.71
3	1935	300.71	55.73
4	1950	307.13	59.74
5	1965	313.55	63.75
6	1980	319.97	68.27
7
8	Single		1945	307.13
9	Not Single		1934	55.73
XLOOKUP

Cell Formulas
Range	Formula
D8:D9	D8	=XLOOKUP(C8,A$2:A$6,IF(A8="Single",B$2:B$6,C$2:C$6),,1)

 

[Fluff]

Glad we could help & thanks for the feedback.