# VLookUp and Match the nearest value.

#### vivienne3608

##### New Member
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><COLGROUP><COL><COL><COL span=5><COL><COL></COLGROUP>

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try such array formula (Ctrl+Shift+Enter committed):
Code:
``=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.

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.

Try such array formula (Ctrl+Shift+Enter committed):
Code:
``=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

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.

You're welcome. Thanks for the feedback.

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><COLGROUP><COL><COL span=8></COLGROUP>

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.

Last edited:
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.
nearest value from a range of values
So this is what my formula did.

Thank you Ingolf your first formula works.

Replies
1
Views
284
Replies
10
Views
52K
Replies
1
Views
591

1,220,954
Messages
6,157,042
Members
451,394
Latest member
indrajeet_rajput

### 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.

### Which adblocker are you using?

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

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