Help on array formula conversion

[dmark224]

hi good day/evening,

I am new to this forums and I want to thank everyone in advance,

can someone help me with my array problem, I wan to be able to compare a cell on a list. with this formula

COLUMN C is the list

APPLE

BANANA

STRAWBERRY

PINEAPPLE

ORANGE

COLUMN D is the what i want to compare to every row on the list
Bananna

COLUMN E is
=len(C2)

=SUMPRODUCT(--(LEFT(C2,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2)))))
this works on side by side comparisson, I would like to compare all what is in the list on column C with what is in the cell in D2. then retaining the maximum value
compare C2=D2 , C3=D2, C4=D2, C5=D2 and so on.


somewhat like this

=MAX(SUMPRODUCT(--(LEFT(C2,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(LEFT(C3,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(LEFT(C4,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(LEFT(C5,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C5,ROW(INDIRECT("c1:c" & E2)),1),D2)))))

another formula that I need to address is

=SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C2,ROW(INDIRECT("c1:c" & E2)),1),D2))))
this works on side by side comparisson, I would like to compare a list on column C with a cell. then retaining the maximum value
compare C2=D2 , C3=D2, C4=D2, C5=D2 and so on.


=MAX(SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C2,ROW(INDIRECT("c1:c" & E2)),1),D2)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C3,ROW(INDIRECT("c1:c" & E2)),1),D2)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C4,ROW(INDIRECT("c1:c" & E2)),1),D2)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C5,ROW(INDIRECT("c1:c" & E2)),1),D2)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C6,ROW(INDIRECT("c1:c" & E2)),1),D2)))))


but the list can go as long as there is data from the list (column c) "may use counta"

I am not using index match because this would only look for exact match. I want to be able to decide the percentage of approximate searching.

Thank you in advance

 

[kweaver]

Welcome to Mr. Excel. It would help anyone trying to assist if you'd (a) use XL2BB to show your sample data and (b) provide expected results.

 

[dmark224]

sorry i have to re-write my question COLUMN E is Len(D2)

hi good day/evening,

I am new to this forums and I want to thank everyone in advance,

can someone help me with my array problem, I wan to be able to compare a cell on a list. with this formula

COLUMN C is the list

APPLE

BANANA

STRAWBERRY

PINEAPPLE

ORANGE

COLUMN D is the what i want to compare to every row on the list
Bananna

COLUMN E is
=len(D2)

=SUMPRODUCT(--(LEFT(C2,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2)))))
this works on side by side comparisson, I would like to compare all what is in the list on column C with what is in the cell in D2. then retaining the maximum value
compare C2=D2 , C3=D2, C4=D2, C5=D2 and so on.




somewhat like this
=MAX(SUMPRODUCT(--(LEFT(C2,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(LEFT(C3,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(LEFT(C4,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(LEFT(C5,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C5,ROW(INDIRECT("c1:c" & E2)),1),D2)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C6,ROW(INDIRECT("c1:c" & E2)),1),D2)))))


and per character

=SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),C2))))
this works on side by side comparisson, I would like to compare all what is in the list on column C with what is in the cell in D2. then retaining the maximum value
compare C2=D2 , C3=D2, C4=D2, C5=D2 and so on.


=MAX(SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),C2)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),C3)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),C4)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),C5)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),C6)))))




but the list can go as long as there is data from the list (column c) "may use counta"

I am not using index match because this would only look for exact match. I want to be able to decide the percentage of approximate searching.

Thank you in advance


attaching excel file.

approximatelookupBetter.xlsx
	A	B	C	D	E	F	G	H	I
1			LIST SUB	COMPARE SUB		compare left to right	compare per character	result that I want with out manual input per row left to right	result that I want with out manual input per row per character
2			APPLE	strawbery	9	0	2	8	9
3			BANANA	baanan	6	2	6	2	6
4			STRAWBERRY	pineapple	9	0	3	9	9
5			PINEAPPLE	orange	6	0	3	6	6
6			ORANGE	apple	5	0	2	5	5
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=LEN(D2)
F2:F6	F2	=SUMPRODUCT(--(LEFT(C2,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2)))))
G2:G6	G2	=SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),C2))))
H2:H6	H2	=MAX(SUMPRODUCT(--(LEFT($C$2,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(LEFT($C$3,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(LEFT($C$4,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(LEFT($C$5,ROW(INDIRECT("c1:c" & E2)))=LEFT(D2,ROW(INDIRECT("c1:c" &E2))))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(C5,ROW(INDIRECT("c1:c" & E2)),1),D2)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID($C$6,ROW(INDIRECT("c1:c" & E2)),1),D2)))))
I2:I6	I2	=MAX(SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),$C$2)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),$C$3)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),$C$4)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),$C$5)))),SUMPRODUCT(--(ISNUMBER(SEARCH(MID(D2,ROW(INDIRECT("c1:c" & E2)),1),$C$6)))))

 

[dmark224]

Anyone please. thanks.