CODEWITHSUNDEEP
excelexcel-formula
Zaur
Zaur

Reputation: 123

Excel closest match with index/match

I have a question regarding the closest match when using index/match formula. I use CONCAT to make a new field out of 3 cells. In the last cell I have a number, varying from 0 to 10.

So I get fields like ABCCBD3,ABCCBD5, ABCCBD7, ABCCBD10 etc. I need to find closes matches for 5 and 10 in the end.

For example, if my INDEX/MATCH lookup finds ABCCBD6 (any number above 5 in the end) it should identify it as ABCCB10. If it finds from ABCCBD to ABCCBD5 it should identify it as ABCCBD5.

Because in my lookup I need to fill columns either "5" or "10". So any result from ABCCBD to ABCCBD5 will go to column named "5" while any result from ABCCBD6 to ABCCBD10 will go to "10" column. Is this something doable?

Here's an example:

enter image description here

Upvotes: 0

Views: 328

Answers (1)

JvdV
JvdV

Reputation: 75990

For example:

enter image description here

Formula in G4, if one has O365:

=LET(X,FILTER($D4:$E9,CEILING($D4:$D9,5)=G3),INDEX(X,0,1)&INDEX(X,0,2))

Drag right.

With earlier versions of Excel, try:

=IFERROR(INDEX($D$1:$D$9,SMALL(IF(CEILING($D$4:$D$9,5)=G$3,ROW(D$4:D$9),""),ROW(A1)))&INDEX($E$1:$E$9,SMALL(IF(CEILING($D$4:$D$9,5)=G$3,ROW(D$4:D$9),""),ROW(A1))),"")

It's an CSE-entered formula. Drag right and down.

Upvotes: 1

Related Questions