VLookUp and Match the nearest value.

V

vivienne3608

New Member
Joined
Sep 10, 2014
Messages
17
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.

[TABLE="width: 671"]
<TBODY>[TR]
[TD] [/TD]
[TD]A</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]E </SPAN>[/TD]
[TD]F</SPAN>[/TD]
[TD]G</SPAN>[/TD]
[TD]H</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD][/TD]
[TD]1</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]Top</SPAN>[/TD]
[TD="align: right"]£37,930</SPAN>[/TD]
[TD="align: right"]£39,830</SPAN>[/TD]
[TD="align: right"]£41,830</SPAN>[/TD]
[TD="align: right"]£44,130</SPAN>[/TD]
[TD][/TD]
[TD]Group</SPAN>[/TD]
[TD]Bottom</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]Top</SPAN>[/TD]
[TD="align: right"]£37,930</SPAN>[/TD]
[TD="align: right"]£39,830</SPAN>[/TD]
[TD="align: right"]£41,830</SPAN>[/TD]
[TD="align: right"]£44,130</SPAN>[/TD]
[TD][/TD]
[TD]Stage</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]Top</SPAN>[/TD]
[TD="align: right"]£37,245</SPAN>[/TD]
[TD="align: right"]£39,145</SPAN>[/TD]
[TD="align: right"]£41,045</SPAN>[/TD]
[TD="align: right"]£43,345</SPAN>[/TD]
[TD][/TD]
[TD]Current Amount</SPAN>[/TD]
[TD]£30,941</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]Top</SPAN>[/TD]
[TD="align: right"]£36,459</SPAN>[/TD]
[TD="align: right"]£38,359</SPAN>[/TD]
[TD="align: right"]£40,259</SPAN>[/TD]
[TD="align: right"]£42,559</SPAN>[/TD]
[TD][/TD]
[TD]Proposed Amount</SPAN>[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD]Top</SPAN>[/TD]
[TD="align: right"]£35,774</SPAN>[/TD]
[TD="align: right"]£37,574</SPAN>[/TD]
[TD="align: right"]£39,374</SPAN>[/TD]
[TD="align: right"]£41,674</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD]Top</SPAN>[/TD]
[TD="align: right"]£34,988</SPAN>[/TD]
[TD="align: right"]£36,688</SPAN>[/TD]
[TD="align: right"]£38,488</SPAN>[/TD]
[TD="align: right"]£40,688</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD]Top</SPAN>[/TD]
[TD="align: right"]£34,988</SPAN>[/TD]
[TD="align: right"]£36,688</SPAN>[/TD]
[TD="align: right"]£38,488</SPAN>[/TD]
[TD="align: right"]£40,688</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9</SPAN>[/TD]
[TD]Bottom</SPAN>[/TD]
[TD="align: right"]£33,334</SPAN>[/TD]
[TD="align: right"]£34,834</SPAN>[/TD]
[TD="align: right"]£36,534</SPAN>[/TD]
[TD="align: right"]£38,634</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD]Bottom</SPAN>[/TD]
[TD="align: right"]£33,334</SPAN>[/TD]
[TD="align: right"]£34,834</SPAN>[/TD]
[TD="align: right"]£36,534</SPAN>[/TD]
[TD="align: right"]£38,634</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11</SPAN>[/TD]
[TD]Bottom</SPAN>[/TD]
[TD="align: right"]£32,758</SPAN>[/TD]
[TD="align: right"]£34,158</SPAN>[/TD]
[TD="align: right"]£35,758</SPAN>[/TD]
[TD="align: right"]£37,858</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]Bottom</SPAN>[/TD]
[TD="align: right"]£32,182</SPAN>[/TD]
[TD="align: right"]£33,582</SPAN>[/TD]
[TD="align: right"]£35,082</SPAN>[/TD]
[TD="align: right"]£37,182</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD]Bottom</SPAN>[/TD]
[TD="align: right"]£31,507</SPAN>[/TD]
[TD="align: right"]£32,807</SPAN>[/TD]
[TD="align: right"]£34,007</SPAN>[/TD]
[TD="align: right"]£35,907</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD]Bottom</SPAN>[/TD]
[TD="align: right"]£30,931</SPAN>[/TD]
[TD="align: right"]£32,031</SPAN>[/TD]
[TD="align: right"]£33,131</SPAN>[/TD]
[TD="align: right"]£34,731</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15</SPAN>[/TD]
[TD]Bottom</SPAN>[/TD]
[TD="align: right"]£30,931</SPAN>[/TD]
[TD="align: right"]£32,031</SPAN>[/TD]
[TD="align: right"]£33,131</SPAN>[/TD]
[TD="align: right"]£34,731</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=5><COL><COL></COLGROUP>[/TABLE]
 
Sort by date Sort by votes
Aladin Akyurek said:
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)))))
Click to expand...

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.
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
vivienne3608 said:
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.
Click to expand...

Thus, not...

[TABLE="width: 53"]
<TBODY>[TR]
[TD="align: right"]33,334</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[TABLE="width: 53"]
<TBODY>[TR]
[TD="align: right"]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
for Bottom, 1, and 34,719 ? If not, how/why 32,182 you appear to think?
 
Upvote 0
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.
 
Upvote 0
vivienne3608 said:
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.
Click to expand...

See my reply in post #20.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Excel Problem
Replies
1
Views
300
vivienne3608
V
S
  • Question Question
Vlookup to return 2nd matching value
2
Replies
10
Views
52K
MIGHTYJOE028
M
A
  • Question Question
Vlookup nearest value issue
Replies
1
Views
601
Oaktree
Oaktree

Forum statistics

Threads
1,225,483
Messages
6,185,263
Members
453,284
Latest member
osy25

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top