• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find closest match from multiple column values and return corresponding column header name

Hello Excel Gurus,

Hope you all keeping well!

I came back after a long time with a new challenge in excel. I tried a lot but I can't wrap my head around it. I want to find the closest match value from multiple column values and return the corresponding column header name.

Appreciate if a quick help on it.


All the best,
Ramesh
 

Attachments

  • Closet Match.xlsx
    10.9 KB · Views: 12
Try,

In C8, formula copied down :

=INDEX(B$1:F$1,MATCH(AGGREGATE(15,6,ABS(INDEX(B$2:F$5,MATCH(A8,A$2:A$5,0),0)-B8),1),INDEX(ABS(INDEX(B$2:F$5,MATCH(A8,A$2:A$5,0),0)-B8),0),0))

View attachment 75777
Awesome!! thanks @bosco_yip for a quick solution. I want to add a context here that if that values are above than ranges then it should shows as "above Lev 5"
for example
Officer has the ranges as lev 1 = 100 , Lev 2 = 250, Lev 3 = 450, Lev 4 = 799 , Lev 5 = 875
and Officer Salary is above 900 then the Lev should show as " Above Lev 5"

Apologies for adding more context to it.
Thanks again for your help!!

All the best
Ramesh
 
Last edited:
Back
Top