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>

 

[vivienne3608]

Nevermind. I guess you mean:

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

=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)))))

The result would also be 32,182. The proposed amount should be equal or higher than the current amount. However there are a few instances when the current amount is higher than the largest proposed option. In that case I want excel to pick that largest proposed option even if it is not equal or greater than the current amount.

 

[Aladin Akyurek]

The result would also be 32,182. The proposed amount should be equal or higher than the current amount. However there are a few instances when the current amount is higher than the largest proposed option. In that case I want excel to pick that largest proposed option even if it is not equal or greater than the current amount.

Thus, not...

33,334</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

for Bottom, 1, and 34,719 ? If not, how/why 32,182 you appear to think?

 

[vivienne3608]

Sorry I was just answering #19 where you asked:

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

"The result would also be 32,182." was answering the above.

So that sentence aside I agree that I need the answer to return 33,334.

 

[Aladin Akyurek]

Sorry I was just answering #19 where you asked:

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

"The result would also be 32,182." was answering the above.

So that sentence aside I agree that I need the answer to return 33,334.

See my reply in post #20.