VLookUp and Match the nearest value.

vivienne3608 · Sep 10, 2014

Hello everyone,

How do I use VLookUp and Match (or any other excel formula) to return the nearest value from a range of values? The answer will be in cell H5. I need excel to use cells H2 and H3 as the search criteria to find the closest value equal to or above the value in cell H4. The table is cells A1:E15. I know there is sometimes more than one equal answer but I need to know the value it doesn't matter how many times it appears. I hope the fact there are more than one cell with the same answer doesn't mean excel can't perform what I need it to.

I appreciate any help with this. Thanks.

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E </SPAN>	F</SPAN>	G</SPAN>	H</SPAN>
1</SPAN>		1</SPAN>	2</SPAN>	3</SPAN>	4</SPAN>
2</SPAN>	Top</SPAN>	£37,930</SPAN>	£39,830</SPAN>	£41,830</SPAN>	£44,130</SPAN>		Group</SPAN>	Bottom</SPAN>
3</SPAN>	Top</SPAN>	£37,930</SPAN>	£39,830</SPAN>	£41,830</SPAN>	£44,130</SPAN>		Stage</SPAN>	2</SPAN>
4</SPAN>	Top</SPAN>	£37,245</SPAN>	£39,145</SPAN>	£41,045</SPAN>	£43,345</SPAN>		Current Amount</SPAN>	£30,941</SPAN>
5</SPAN>	Top</SPAN>	£36,459</SPAN>	£38,359</SPAN>	£40,259</SPAN>	£42,559</SPAN>		Proposed Amount</SPAN>	?
6</SPAN>	Top</SPAN>	£35,774</SPAN>	£37,574</SPAN>	£39,374</SPAN>	£41,674</SPAN>
7</SPAN>	Top</SPAN>	£34,988</SPAN>	£36,688</SPAN>	£38,488</SPAN>	£40,688</SPAN>
8</SPAN>	Top</SPAN>	£34,988</SPAN>	£36,688</SPAN>	£38,488</SPAN>	£40,688</SPAN>
9</SPAN>	Bottom</SPAN>	£33,334</SPAN>	£34,834</SPAN>	£36,534</SPAN>	£38,634</SPAN>
10</SPAN>	Bottom</SPAN>	£33,334</SPAN>	£34,834</SPAN>	£36,534</SPAN>	£38,634</SPAN>
11</SPAN>	Bottom</SPAN>	£32,758</SPAN>	£34,158</SPAN>	£35,758</SPAN>	£37,858</SPAN>
12</SPAN>	Bottom</SPAN>	£32,182</SPAN>	£33,582</SPAN>	£35,082</SPAN>	£37,182</SPAN>
13</SPAN>	Bottom</SPAN>	£31,507</SPAN>	£32,807</SPAN>	£34,007</SPAN>	£35,907</SPAN>
14</SPAN>	Bottom</SPAN>	£30,931</SPAN>	£32,031</SPAN>	£33,131</SPAN>	£34,731</SPAN>
15</SPAN>	Bottom</SPAN>	£30,931</SPAN>	£32,031</SPAN>	£33,131</SPAN>	£34,731</SPAN>

Ingolf · Sep 10, 2014

Second formula has a typo... should be:

=IFERROR(OFFSET($A$2,MATCH($H$3,CHOOSE(MATCH($H$2,$B$2:$E$2,0),B3:B16,C3:C16,D3:D16,E3:E16),0),),"-")

If your excel version is > 2007

vivienne3608 · Sep 17, 2014

Good morning all,

I hope someone can help again with this, the following formula works:

=MIN(IF('Data Sheet'!BO5:BO61=B9,IF('Data Sheet'!BQ4:BT4=H3,IF('Data Sheet'!BQ5:BT61>=E7,'Data Sheet'!BQ5:BT61))))

However, if there is not a value higher it returns a N/A. How do I get the formula to select the highest figure in the range if there is not one higher than the value required.

I hope that makes sense.

Viv

vivienne3608 · Sep 17, 2014

vivienne3608 said:
Good morning all,

I hope someone can help again with this, the following formula works:

=MIN(IF('Data Sheet'!BO5:BO61=B9,IF('Data Sheet'!BQ4:BT4=H3,IF('Data Sheet'!BQ5:BT61>=E7,'Data Sheet'!BQ5:BT61))))

However, if there is not a value higher it returns a N/A. How do I get the formula to select the highest figure in the range if there is not one higher than the value required.

I hope that makes sense.

Viv

Hello everyone,

How do I use VLookUp and Match (or any other excel formula) to return the nearest value from a range of values? The answer will be in cell H5. I need excel to use cells H2 and H3 as the search criteria to find the closest value equal to or above the value in cell H4. The table is cells A1:E15. I know there is sometimes more than one equal answer but I need to know the value it doesn't matter how many times it appears. I hope the fact there are more than one cell with the same answer doesn't mean excel can't perform what I need it to.

I appreciate any help with this. Thanks.

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E </SPAN>	F</SPAN>	G</SPAN>	H</SPAN>
1</SPAN>		1</SPAN>	2</SPAN>	3</SPAN>	4</SPAN>
2</SPAN>	Top</SPAN>	£37,930</SPAN>	£39,830</SPAN>	£41,830</SPAN>	£44,130</SPAN>		Group</SPAN>	Bottom</SPAN>
3</SPAN>	Top</SPAN>	£37,930</SPAN>	£39,830</SPAN>	£41,830</SPAN>	£44,130</SPAN>		Stage</SPAN>	2</SPAN>
4</SPAN>	Top</SPAN>	£37,245</SPAN>	£39,145</SPAN>	£41,045</SPAN>	£43,345</SPAN>		Current Amount</SPAN>	£30,941</SPAN>
5</SPAN>	Top</SPAN>	£36,459</SPAN>	£38,359</SPAN>	£40,259</SPAN>	£42,559</SPAN>		Proposed Amount</SPAN>	?
6</SPAN>	Top</SPAN>	£35,774</SPAN>	£37,574</SPAN>	£39,374</SPAN>	£41,674</SPAN>
7</SPAN>	Top</SPAN>	£34,988</SPAN>	£36,688</SPAN>	£38,488</SPAN>	£40,688</SPAN>
8</SPAN>	Top</SPAN>	£34,988</SPAN>	£36,688</SPAN>	£38,488</SPAN>	£40,688</SPAN>
9</SPAN>	Bottom</SPAN>	£33,334</SPAN>	£34,834</SPAN>	£36,534</SPAN>	£38,634</SPAN>
10</SPAN>	Bottom</SPAN>	£33,334</SPAN>	£34,834</SPAN>	£36,534</SPAN>	£38,634</SPAN>
11</SPAN>	Bottom</SPAN>	£32,758</SPAN>	£34,158</SPAN>	£35,758</SPAN>	£37,858</SPAN>
12</SPAN>	Bottom</SPAN>	£32,182</SPAN>	£33,582</SPAN>	£35,082</SPAN>	£37,182</SPAN>
13</SPAN>	Bottom</SPAN>	£31,507</SPAN>	£32,807</SPAN>	£34,007</SPAN>	£35,907</SPAN>
14</SPAN>	Bottom</SPAN>	£30,931</SPAN>	£32,031</SPAN>	£33,131</SPAN>	£34,731</SPAN>
15</SPAN>	Bottom</SPAN>	£30,931</SPAN>	£32,031</SPAN>	£33,131</SPAN>	£34,731</SPAN>

Aladin Akyurek · Sep 17, 2014

vivienne3608 said:
Hello everyone,

How do I use VLookUp and Match (or any other excel formula) to return the nearest value from a range of values? The answer will be in cell H5. I need excel to use cells H2 and H3 as the search criteria to find the closest value equal to or above the value in cell H4. The table is cells A1:E15. I know there is sometimes more than one equal answer but I need to know the value it doesn't matter how many times it appears. I hope the fact there are more than one cell with the same answer doesn't mean excel can't perform what I need it to.

I appreciate any help with this. Thanks.

A
B
C
D
E 
F
G
H

1
1
2
3
4

2
Top
£37,930
£39,830
£41,830
£44,130
Group
Bottom

3
Top
£37,930
£39,830
£41,830
£44,130
Stage
2

4
Top
£37,245
£39,145
£41,045
£43,345
Current Amount
£30,941

5
Top
£36,459
£38,359
£40,259
£42,559
Proposed Amount
?

6
Top
£35,774
£37,574
£39,374
£41,674

7
Top
£34,988
£36,688
£38,488
£40,688

8
Top
£34,988
£36,688
£38,488
£40,688

9
Bottom
£33,334
£34,834
£36,534
£38,634

10
Bottom
£33,334
£34,834
£36,534
£38,634

11
Bottom
£32,758
£34,158
£35,758
£37,858

12
Bottom
£32,182
£33,582
£35,082
£37,182

13
Bottom
£31,507
£32,807
£34,007
£35,907

14
Bottom
£30,931
£32,031
£33,131
£34,731

15
Bottom
£30,931
£32,031
£33,131
£34,731

<TBODY>
</TBODY>

I suppose the desire value is: 32,031

H4, control+shift+enter, not just enter:

Rich (BB code):

=MIN(IF($A$2:$A$15=H2,IF(INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0))>=H4,
  INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)))))

Aladin Akyurek · Sep 17, 2014

vivienne3608 said:
Thank you for the quick replies above. I have another question that I need help with. In the sheet below I need the answer in H4 and I need excel to look at the stage (H2) and the proposed amount (H3), match this in the cells B3:E16 and find the central value in that row from column A.

I apologise for the format alignment. Thank you.

Vivienne

A
B
C
D
E
F
G
H

1
Proposed Amount

2
Central
1
2
3
4
Stage
2

3
£33,830
£37,930
£39,830
£41,830
£44,130
Proposed Amount
£32,031

4
£33,830
£37,930
£39,830
£41,830
£44,130
Central

5
£33,145
£37,245
£39,145
£41,045
£43,345

6
£32,459
£36,459
£38,359
£40,259
£42,559

7
£31,774
£35,774
£37,574
£39,374
£41,674

8
£31,088
£34,988
£36,688
£38,488
£40,688

9
£31,088
£34,988
£36,688
£38,488
£40,688

10
£29,534
£33,334
£34,834
£36,534
£38,634

11
£29,534
£33,334
£34,834
£36,534
£38,634

12
£28,958
£32,758
£34,158
£35,758
£37,858

13
£28,382
£32,182
£33,582
£35,082
£37,182

14
£27,807
£31,507
£32,807
£34,007
£35,907

15
£27,231
£30,931
£32,031
£33,131
£34,731

16
£27,231
£30,931
£32,031
£33,131
£34,731

<TBODY>
</TBODY>

H4, just enter:

=INDEX($A$3:$A$16,MATCH(H3,INDEX($B$3:$E$16,0,MATCH(H2,$B$2:$E$2,0)),0))

which is not volatile.

vivienne3608 · Sep 17, 2014

Thanks for the reply. I need the formula to try and pick the closest greater proposed value to the current value and the previous formula I had worked =MIN(IF(A2:A15=H2,IF(B1:E1=H3,IF(B2:E15>=H4,B2:E15)))). However I have now noticed that in my larger data table (the table displayed is just a snapshot) the current value could be higher than the largest value for the proposed value options. I therefore need the formula to do as the above formula but to also pick the highest proposed value for that particular group and stage if there is no higher option than the current value. The following formula returns a zero figure:<o

></o

>
<o

> </o

>
=MIN(IF($A$2:$A$15=H2,IF(INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0))>=H4,<o

></o

>
INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)))))<o

></o

>

Aladin Akyurek · Sep 17, 2014

vivienne3608 said:
Thanks for the reply. I need the formula to try and pick the closest greater proposed value to the current value and the previous formula I had worked =MIN(IF(A2:A15=H2,IF(B1:E1=H3,IF(B2:E15>=H4,B2:E15)))). However I have now noticed that in my larger data table (the table displayed is just a snapshot) the current value could be higher than the largest value for the proposed value options. I therefore need the formula to do as the above formula but to also pick the highest proposed value for that particular group and stage if there is no higher option than the current value. The following formula returns a zero figure:<o></o>
<o></o>
=MIN(IF($A$2:$A$15=H2,IF(INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0))>=H4,<o></o>
INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)))))<o></o>

You probably did not apply control+shift+enter. Also, you do not seem to have noticed the reported result. Another point: Try to post the desired value that you want to see instead of just a question mark.

See the workbook that implement the proposed dormulas:
https://dl.dropboxusercontent.com/u/65698317/vivienne3608%20proposed%20amount%20%2B%20central.xlsx

vivienne3608 · Sep 18, 2014

Thanks for the reply. I need the formula to try and pick the closest greater proposed value to the current value and the previous formula I had worked =MIN(IF(A2:A15=H2,IF(B1:E1=H3,IF(B2:E15>=H4,B2:E15)))). However I have now noticed that in my larger data table (the table displayed is just a snapshot) the current value could be higher than the largest value for the proposed value options. I therefore need the formula to do as the above formula but to also pick the highest proposed value for that particular group and stage if there is no higher option than the current value. The following formula returns a zero figure:

=MIN(IF($A$2:$A$15=H2,IF(INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0))>=H4,
INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)))))

The table below shows the higher current value and the proposed amount I need the formula to return. I know the proposed value I need appears twice, however, it doesn’t matter which cell the formula picks. The cells B2:E15 contain the proposed amounts.

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>
1</SPAN>		1</SPAN>	2</SPAN>	3</SPAN>	4</SPAN>
2</SPAN>	Top</SPAN>	37,930</SPAN>	39,830</SPAN>	41,830</SPAN>	44,130</SPAN>		Group</SPAN>	Bottom</SPAN>
3</SPAN>	Top</SPAN>	37,930</SPAN>	39,830</SPAN>	41,830</SPAN>	44,130</SPAN>		Stage</SPAN>	1</SPAN>
4</SPAN>	Top</SPAN>	37,245</SPAN>	39,145</SPAN>	41,045</SPAN>	43,345</SPAN>		Current Amount</SPAN>	34,719</SPAN>
5</SPAN>	Top</SPAN>	36,459</SPAN>	38,359</SPAN>	40,259</SPAN>	42,559</SPAN>		Proposed Amount</SPAN>	33,334</SPAN>
6</SPAN>	Top</SPAN>	35,774</SPAN>	37,574</SPAN>	39,374</SPAN>	41,674</SPAN>
7</SPAN>	Top</SPAN>	34,988</SPAN>	36,688</SPAN>	38,488</SPAN>	40,688</SPAN>
8</SPAN>	Top</SPAN>	34,988</SPAN>	36,688</SPAN>	38,488</SPAN>	40,688</SPAN>
9</SPAN>	Bottom</SPAN>	33,334</SPAN>	34,834</SPAN>	36,534</SPAN>	38,634</SPAN>
10</SPAN>	Bottom</SPAN>	33,334</SPAN>	34,834</SPAN>	36,534</SPAN>	38,634</SPAN>
11</SPAN>	Bottom</SPAN>	32,758</SPAN>	34,158</SPAN>	35,758</SPAN>	37,858</SPAN>
12</SPAN>	Bottom</SPAN>	32,182</SPAN>	33,582</SPAN>	35,082</SPAN>	37,182</SPAN>
13</SPAN>	Bottom</SPAN>	31,507</SPAN>	32,807</SPAN>	34,007</SPAN>	35,907</SPAN>
14</SPAN>	Bottom</SPAN>	30,931</SPAN>	32,031</SPAN>	33,131</SPAN>	34,731</SPAN>
15</SPAN>	Bottom</SPAN>	30,931</SPAN>	32,031</SPAN>	33,131</SPAN>	34,731</SPAN>

Aladin Akyurek · Sep 18, 2014

vivienne3608 said:
Thanks for the reply. I need the formula to try and pick the closest greater proposed value to the current value and the previous formula I had worked =MIN(IF(A2:A15=H2,IF(B1:E1=H3,IF(B2:E15>=H4,B2:E15)))). However I have now noticed that in my larger data table (the table displayed is just a snapshot) the current value could be higher than the largest value for the proposed value options. I therefore need the formula to do as the above formula but to also pick the highest proposed value for that particular group and stage if there is no higher option than the current value. The following formula returns a zero figure:

=MIN(IF($A$2:$A$15=H2,IF(INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0))>=H4,
INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)))))

The table below shows the higher current value and the proposed amount I need the formula to return. I know the proposed value I need appears twice, however, it doesn’t matter which cell the formula picks. The cells B2:E15 contain the proposed amounts.

A
B
C
D
E
F
G
H

1
1
2
3
4

2
Top
37,930
39,830
41,830
44,130
Group
Bottom

3
Top
37,930
39,830
41,830
44,130
Stage
1

4
Top
37,245
39,145
41,045
43,345
Current Amount
34,719

5
Top
36,459
38,359
40,259
42,559
Proposed Amount
33,334

6
Top
35,774
37,574
39,374
41,674

7
Top
34,988
36,688
38,488
40,688

8
Top
34,988
36,688
38,488
40,688

9
Bottom
33,334
34,834
36,534
38,634

10
Bottom
33,334
34,834
36,534
38,634

11
Bottom
32,758
34,158
35,758
37,858

12
Bottom
32,182
33,582
35,082
37,182

13
Bottom
31,507
32,807
34,007
35,907

14
Bottom
30,931
32,031
33,131
34,731

15
Bottom
30,931
32,031
33,131
34,731

<TBODY>
</TBODY>

What would be the result (the proposed amount) if we had 32,182 as Current Amount?

Aladin Akyurek · Sep 18, 2014

Aladin Akyurek said:
What would be the result (the proposed amount) if we had 32,182 as Current Amount?

Nevermind. I guess you mean:

H5, control+shift+enter, not just enter:

Rich (BB code):

=IFERROR(1/(1/MIN(IF($A$2:$A$15=H2,
  IF(INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0))>=H4,
  INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)))))),
  MAX(IF($A$2:$A$15=H2,INDEX($B$2:$E$15,0,MATCH(H3,$B$1:$E$1,0)))))

VLookUp and Match the nearest value.

vivienne3608

New Member

Ingolf

Banned

Excel Facts

vivienne3608

New Member

vivienne3608

New Member

Aladin Akyurek

MrExcel MVP

Aladin Akyurek

MrExcel MVP

vivienne3608

New Member

Aladin Akyurek

MrExcel MVP

vivienne3608

New Member

Aladin Akyurek

MrExcel MVP

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

VLookUp and Match the nearest value.

New Member

Banned

Excel Facts

New Member

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock