Index Match/Vlookup has return column with no value, has to return closest number

Nardo

New Member
Joined
Nov 21, 2013
Messages
8
1395-6.9030.14
13946.17-30.15
1393--30.16
1392--30.17
1391-6.9130.18
1390--30.19
1389--30.20
13886.186.9230.21
1387--30.22
1386--30.23
1385-6.9330.24
1384--30.25
1383--30.26
13826.196.9430.27
1381--30.28
1380--30.29
1379-6.9530.30
1378--30.31
1377--30.32
13766.206.9630.33
1375--30.34

<tbody>
</tbody>


Hello,

I need help finding a vlookup or index match formula to the first two columns for example.

If lookup value in 1st column is:
-1390 then return the closest value in 2nd column, which will be 6.18
-1392 then return the closest value in 2nd column, which will be 6.17
-1391 then return the closest value south in 2nd column, which will be 6.18

Having the cells with dashes in them are confusing me on what to do, and I cannot change the table data because the first column will correspond with many other columns which may or may not contain any dashes, as shown in the 3rd and 4th columns.

Thank you all in advance
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

sgmpatnaik

Board Regular
Joined
Jul 6, 2012
Messages
75
@Nardo

Welcome to the Forum, Glad you are here

Say your desire data is Consist from A1:C50 and you place a value of Column A in Column D1 for 1390

then you can find the value as in F1

=INDEX($b$2:$b$50,MATCH(MIN(ABS($a$2:$a$50-D1)),ABS($a$2:$a$50-D1),0))

Then you will get the closet value of Lookup column of D1

Hope It solve your problem, if any problem please inform

Thanks

Patnaik
 

Nardo

New Member
Joined
Nov 21, 2013
Messages
8
Thank you for the help, but i am confused.

I have used this as a test for reference

=INDEX(B3:B50,MATCH(AF7,A3:A50,0))

-when I enter the value of 1388, then the return value is 6.18

-But, if i enter the value of 1390, i get a return value of (-), I want the return value of the closest value in the 2nd column which is 6.18
-eg. If I enter the value 1375, i want the return to be 6.20
 

Watch MrExcel Video

Forum statistics

Threads
1,099,697
Messages
5,470,228
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top