# Match Next Closest higher Value in Array

#### ootkhopdi

##### Board Regular
Hello

i have a table As Below
 B C D E F 1800 1900 2000 2400 2800 7000 7730 8460 9910 11360 7210 7970 8720 10210 11700 7430 8210 8990 10520 12050 7660 8460 9260 10840 12420 7890 8720 9540 11170 12800 8130 8990 9830 11510 13190

<tbody>
</tbody>

<tbody>
</tbody>

Cell value of B7 = 1800
Cell value of C7 = 7200
now i want to get Match value in Cell D7 like =Match(c7,b1:f6,0) answer will be 7210
if i change value of B7= 2000 then answer in Cell d7 will be 8460

How can i find correct value...

i want to retrieve next closest higher value in table
as if i want to type value in cell b8 is
match(

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

##### MrExcel MVP
Assuming that the sample is located in B1:F7, would you put up the look up values in H1 and in I1 and specify the result that must obtain in J1? Please do not refer to any fomula while replying.

#### ootkhopdi

##### Board Regular
yes i can put values in H1, I1 and result in J1

Last edited:

##### MrExcel MVP
yes i can put values in H1, I1 and result in J1

H1 = 1800

I1 = 7200

Result = 7210

How this result obtains given B1:F7?

#### ootkhopdi

##### Board Regular
sorry,, i think you giving me solution , but after your reply it seem you want solution by me

i am new in excel , so i want solution,,'
this is my problem

how can i get result in J1 as my problem using Match function

#### jimrward

##### Well-known Member
Can I suggest that you read back thru your original post and try and describe the layout of table and the layout of your data as it does not make sense as many times as I have read thru it paying particular attention to cells you have named

Last edited:

#### ootkhopdi

##### Board Regular
yes i m waiting ..
for a positive solution

#### jimrward

##### Well-known Member
After much looking in my crystal ball the mist is clearing and I think I might be understanding the request

Cell B7 contains the column you wish to lookup in using a value which is the first cell in that column so first we have to match B7 with the first row, once found we then look down that column to closest match to value in C7

Where does your lookup table live B? To F?

Am I understanding your question now

#### ootkhopdi

##### Board Regular
i think i want to clear more my question

as my problem..

i want row number value= D7, in column which contain value = B7 in Row 2, and cell value is equal or closest higher with =C7
if Cell value B7=2000,c7=8500 or 8720,, the result will be 2 in D7,,

it like hlookup(b7,b2:f7:,row) where row = value contain in cell equal or closest higher = C7

Replies
2
Views
2K

1,191,172
Messages
5,985,086
Members
439,940
Latest member

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