Help with INDEX MATCH to find absolute closest value

[Maverick27]

I need help troubleshooting 2 diff "Index Match Formulas" to find closest match btwn Col A & Col C of my worksheet

• The range in Col C is where I want to find the specific value.
  

• A2 is the searching value that I want to be compared with (Function to be copied across down the Col)
  

The 2 functions I'm trying to troublesheet :

[FONT=verdana]=INDEX(C2:C20000,MATCH(MIN(ABS(C2:C20000-A2)),ABS(C2:C20000-A2),0))[/FONT]

[FONT=verdana]=INDEX(A2:A10000,MATCH(TRUE,ABS(C2:C20000-A2)=MIN(ABS(C2:C20000-A2)),0))[/FONT]

Unfortunately both functions are returning a result of "#VALUE!"

The expected result for closest match to 876-SCALE in A2 should be: 0876-SCALE (Row 5449 of Col C)



Somebody pls help

 

[AlanY]

the abs() function works with numbers only but your data are both numbers and texts thus the #value errors

 

[Maverick27]

Below is download link of sample sheet.

https://drive.google.com/file/d/0Bx50LQePdgafdjJtYkV2YloyYjQ/view?usp=sharing

 

[Maverick27]

the abs() function works with numbers only but your data are both numbers and texts thus the #value errors

Thanks -that's a start.

Is there another Function I could use ?

Sample Sheet download link.

 

[AlanY]

have a look of the Fuzzy Matching that might help

http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html