CODEWITHSUNDEEP
excelexcel-formula
www.friend0.in
www.friend0.in

Reputation: 257

Finding header/title of nearest number from a row

Excel

Goal is to find the heading/title of a cell matching exactly with a other cell from the same row. In the above image the pink data is available, the user inputs in the green field and the function should show the output in the blue column; i.e From the first pink row the number 12 matches with the last pink column of the same row which has a heading/title as 2021Rating and it is shown. Similarly for the last row the number 15 does not exist in the same row as a result NOT FOUND is shown.

This task somehow can be accomplished with formula like =IF(B2=G2,"2017Rating", IF(C2=G2,"2018Rating...) but is there any better way ?
What if I want the closest match with threshold value of x% in case exact number is not found in the row ? (i.e instead of NOT FOUND the result should be 2017Rating as 14 closest to 15)

Upvotes: 0

Views: 239

Answers (1)

Harun24hr
Harun24hr

Reputation: 36870

If you have Excel-365 then can use XLOOKUP().

=XLOOKUP(G2,B2:F2,$B$1:$F$1,"Not Found")

For other version of excel use INDEX/MATCH like

=IFERROR(INDEX($B$1:$F$1,,MATCH(G2,B2:F2,0)),"Not Found")

Also FILTER() function will work like

=@FILTER($B$1:$F$1,B2:F2=G2,"Not Found")

enter image description here

Upvotes: 1

Related Questions