Find lowest LEN difference & return contents of cell to left

[ellison]

Hi, we are tidying up some data and would like to find (for each row) the Suggested-Match associated with the lowest "LEN difference".

The Suggested Matches are in Columns B, D & F (although some are blank).
The respective "LEN difference" values are in Columns C, E & G.
The lowest "LEN difference" for the row is in Column H
And we would like to return the contents of the cell which is "1 cell to the left" of the minimum LEN difference for that row (or return "_problem" if there are no Suggested Matches)

Please note that sometimes there are:
- blanks cell in the potential matches
- no potential matches at all (in which case return: "_problem")
- 2 cells which share the same lowest LEN value (in which case, return the contents of the first of the Suggested Matches)
- the LEN value can be a negative number

Hopefully this may look a bit clearer in a table!

Any help you could provide would be very much appreciated....

find-lowest-len-difference-&-return-contents-of-cell-to-right-question.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Row	Suggested-Match-A	Match-A LEN difference	Suggested-Match-B	Match-B LEN difference	Suggested-Match-C	Match-C LEN difference	Lowest LEN Difference	TARGET via formula	TARGET manually	Explanation
2	2	AB12 generic	8	AB12	0	AB12-04	3	0		AB12	Lowest LEN difference on this row is "0" in cell E2, so we would like to return the contents of cell D2
3	3					XYZ-789	1	1		XYZ-789	Match-A and Match B are blank, but the lowest LEN difference is the contents of cell F3
4	4							0		_problem	No potential matches offered, so return: "_problem"
5	5			73P_	1	73P-	1	1		73PC	2 same values are the lowest LEN difference, please return the 1st value "on the left": cell D2
6	6	26-10-BETA	-2	2610BETA	0			-2		26-10-BETA	The lowest LEN match is the "-2" in cell C6, so please return the contents of cell B6
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=ROW()
H2:H6	H2	=MIN(C2,E2,G2)

 

[Sufiyan97]

Try

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Row	Suggested-Match-A	Match-A LEN difference	Suggested-Match-B	Match-B LEN difference	Suggested-Match-C	Match-C LEN difference	Lowest LEN Difference	TARGET via formula	TARGET manually	Explanation
2	2	AB12 generic	8	AB12	0	AB12-04	3	0	AB12	AB12	Lowest LEN difference on this row is "0" in cell E2, so we would like to return the contents of cell D2
3	3					XYZ-789	1	1	XYZ-789	XYZ-789	Match-A and Match B are blank, but the lowest LEN difference is the contents of cell F3
4	4							0	_problem	_problem	No potential matches offered, so return: "_problem"
5	5			73P_	1	73P-	1	1	73P_	73PC	2 same values are the lowest LEN difference, please return the 1st value "on the left": cell D2
6	6	26-10-BETA	-2	2610BETA	0			-2	26-10-BETA	26-10-BETA	The lowest LEN match is the "-2" in cell C6, so please return the contents of cell B6
7
Sheet1

Cell Formulas
Range	Formula
H2:H6	H2	=MIN(C2,E2,G2)
I2:I6	I2	=IF(INDEX(B2:H2,,MATCH(MIN(C2,E2,G2),B2:H2,0)-1)=0,"_problem",INDEX(B2:H2,,MATCH(MIN(C2,E2,G2),B2:H2,0)-1))
A2:A6	A2	=ROW()

 

[ellison]

Wow that looks amazing - nearly there! I'm getting an error on Row 4.

i.e. the formula returns "#N/A" rather than "_problem"

 

[Sufiyan97]

Can you post all the data including the data with error

 

[ellison]

Many thanks for coming back Sufiyan, I have no idea why I was getting the error message but I have just tried it out on a few different sets of data and everything is fine.
Gremlin must have been at my end!

Solution marked up as above.

Many, many thanks!

 

[Sufiyan97]

You're welcome!