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

[Nardo]

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]1395[/TD]
[TD]-[/TD]
[TD]6.90[/TD]
[TD]30.14[/TD]
[/TR]
[TR]
[TD]1394[/TD]
[TD]6.17[/TD]
[TD]-[/TD]
[TD]30.15[/TD]
[/TR]
[TR]
[TD]1393[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.16[/TD]
[/TR]
[TR]
[TD]1392[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.17[/TD]
[/TR]
[TR]
[TD]1391[/TD]
[TD]-[/TD]
[TD]6.91[/TD]
[TD]30.18[/TD]
[/TR]
[TR]
[TD]1390[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.19[/TD]
[/TR]
[TR]
[TD]1389[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.20[/TD]
[/TR]
[TR]
[TD]1388[/TD]
[TD]6.18[/TD]
[TD]6.92[/TD]
[TD]30.21[/TD]
[/TR]
[TR]
[TD]1387[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.22[/TD]
[/TR]
[TR]
[TD]1386[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.23[/TD]
[/TR]
[TR]
[TD]1385[/TD]
[TD]-[/TD]
[TD]6.93[/TD]
[TD]30.24[/TD]
[/TR]
[TR]
[TD]1384[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.25[/TD]
[/TR]
[TR]
[TD]1383[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.26[/TD]
[/TR]
[TR]
[TD]1382[/TD]
[TD]6.19[/TD]
[TD]6.94[/TD]
[TD]30.27[/TD]
[/TR]
[TR]
[TD]1381[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.28[/TD]
[/TR]
[TR]
[TD]1380[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.29[/TD]
[/TR]
[TR]
[TD]1379[/TD]
[TD]-[/TD]
[TD]6.95[/TD]
[TD]30.30[/TD]
[/TR]
[TR]
[TD]1378[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.31[/TD]
[/TR]
[TR]
[TD]1377[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.32[/TD]
[/TR]
[TR]
[TD]1376[/TD]
[TD]6.20[/TD]
[TD]6.96[/TD]
[TD]30.33[/TD]
[/TR]
[TR]
[TD]1375[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]30.34[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[sgmpatnaik]

@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]

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