VLookUp and Match the nearest value.

[vivienne3608]

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>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=5><COL><COL></COLGROUP>

 

[Kaper]

Try such array formula (Ctrl+Shift+Enter committed):

=OFFSET(A1,MATCH(MIN(ABS(IF(A2:A15=H2,OFFSET(A2:A15,0,H3),0)-H4)),ABS(IF(A2:A15=H2,OFFSET(A2:A15,0,H3),0)-H4),0),H3)

it returns a value from the column defined by a stage and from row where group agrees and where minimum absolute difference between gurrent and proposed amount has been found.

 

[pgc01]

Hi Vivienne
Welcome to the board

Try in H5:

=MIN(IF(A2:A15=H2,IF(B1:E1=H3,IF(B2:E15>=H4,B2:E15))))


This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

 

[vivienne3608]

Try such array formula (Ctrl+Shift+Enter committed):

=OFFSET(A1,MATCH(MIN(ABS(IF(A2:A15=H2,OFFSET(A2:A15,0,H3),0)-H4)),ABS(IF(A2:A15=H2,OFFSET(A2:A15,0,H3),0)-H4),0),H3)

it returns a value from the column defined by a stage and from row where group agrees and where minimum absolute difference between gurrent and proposed amount has been found.

Thank you so much for such a quick reply.

This works but it can return a value below the value in cell H4 and I need a value equal to or above but it can't be a value below.

Regards

Vivienne

 

[vivienne3608]

PGC,

Thank you very much, this does work. This forum is brilliant and has saved me so much time (and frustration).

Vivienne

Hi Vivienne
Welcome to the board

Try in H5:

=MIN(IF(A2:A15=H2,IF(B1:E1=H3,IF(B2:E15>=H4,B2:E15))))


This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[vivienne3608]

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</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>
1</SPAN>		Proposed Amount</SPAN>
2</SPAN>	Central</SPAN>	1</SPAN>	2</SPAN>	3</SPAN>	4</SPAN>		Stage</SPAN>	2</SPAN>
3</SPAN>	£33,830</SPAN>	£37,930</SPAN>	£39,830</SPAN>	£41,830</SPAN>	£44,130</SPAN>		Proposed Amount</SPAN>	£32,031</SPAN>
4</SPAN>	£33,830</SPAN>	£37,930</SPAN>	£39,830</SPAN>	£41,830</SPAN>	£44,130</SPAN>		Central</SPAN>
5</SPAN>	£33,145</SPAN>	£37,245</SPAN>	£39,145</SPAN>	£41,045</SPAN>	£43,345</SPAN>
6</SPAN>	£32,459</SPAN>	£36,459</SPAN>	£38,359</SPAN>	£40,259</SPAN>	£42,559</SPAN>
7</SPAN>	£31,774</SPAN>	£35,774</SPAN>	£37,574</SPAN>	£39,374</SPAN>	£41,674</SPAN>
8</SPAN>	£31,088</SPAN>	£34,988</SPAN>	£36,688</SPAN>	£38,488</SPAN>	£40,688</SPAN>
9</SPAN>	£31,088</SPAN>	£34,988</SPAN>	£36,688</SPAN>	£38,488</SPAN>	£40,688</SPAN>
10</SPAN>	£29,534</SPAN>	£33,334</SPAN>	£34,834</SPAN>	£36,534</SPAN>	£38,634</SPAN>
11</SPAN>	£29,534</SPAN>	£33,334</SPAN>	£34,834</SPAN>	£36,534</SPAN>	£38,634</SPAN>
12</SPAN>	£28,958</SPAN>	£32,758</SPAN>	£34,158</SPAN>	£35,758</SPAN>	£37,858</SPAN>
13</SPAN>	£28,382</SPAN>	£32,182</SPAN>	£33,582</SPAN>	£35,082</SPAN>	£37,182</SPAN>
14</SPAN>	£27,807</SPAN>	£31,507</SPAN>	£32,807</SPAN>	£34,007</SPAN>	£35,907</SPAN>
15</SPAN>	£27,231</SPAN>	£30,931</SPAN>	£32,031</SPAN>	£33,131</SPAN>	£34,731</SPAN>
16</SPAN>	£27,231</SPAN>	£30,931</SPAN>	£32,031</SPAN>	£33,131</SPAN>	£34,731</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=8></COLGROUP>

 

[Ingolf]

In H4 =OFFSET($A$2,MATCH($H$3,CHOOSE(MATCH($H$2,$B$2:$E$2,0),B3:B16,C3:C16,D3:D16,E3:E16),0),)
or
=IFERRR(OFFSET($A$2,MATCH($H$3,CHOOSE(MATCH($H$2,$B$2:$E$2,0),B3:B16,C3:C16,D3:D16,E3:E16),0),),"-")
depends on your version of excel.

 

[Kaper]

Hi Vivienne,

This works but it can return a value below the value in cell H4 and I need a value equal to or above but it can't be a value below.

But before you asked for:

nearest value from a range of values

So this is what my formula did.

 

[vivienne3608]

Thank you Ingolf your first formula works.