Comparing two columns with part of text

[NickvdB]

Hello,

We are having two databases we would like to compare, does anybody know a good formula or solution to give the Desired result? So basiccally what I would say it should compare the numbers and than give the complete cell of the Table to compare the data with.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Description[/TD]
[TD]Desired result[/TD]
[TD][/TD]
[TD][/TD]
[TD]Table to compare the data with[/TD]
[/TR]
[TR]
[TD]PO 12345 bla bla[/TD]
[TD]PO-12345[/TD]
[TD][/TD]
[TD][/TD]
[TD]12458[/TD]
[/TR]
[TR]
[TD]bla bla PO12458 bla bla[/TD]
[TD]12458[/TD]
[TD][/TD]
[TD][/TD]
[TD]123821[/TD]
[/TR]
[TR]
[TD]bla bla bla POS-12345[/TD]
[TD]PO-12345[/TD]
[TD][/TD]
[TD][/TD]
[TD]POI-124591[/TD]
[/TR]
[TR]
[TD]bla bla bla bla PO-99999[/TD]
[TD]Not found[/TD]
[TD][/TD]
[TD][/TD]
[TD]PO-12345[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

You can try with help column like this :

F2 =LOOKUP(9^9,0+MID(E2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},E2&1/19)),ROW($1:$16)))

B2 Ctrl+Shift+Enter NOT just Enter =IFERROR(INDEX($E$2:$E$5,MATCH(LOOKUP(9^9,0+MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1/19)),ROW($1:$16))),$F$2:$F$5,0)),"Not found")


[TABLE="width: 760"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E [/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Description[/TD]
[TD]Desired result[/TD]
[TD][/TD]
[TD][/TD]
[TD]Table to compare [/TD]
[TD]Help1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PO 12345 bla bla[/TD]
[TD]PO-12345[/TD]
[TD][/TD]
[TD][/TD]
[TD]12458[/TD]
[TD]12458[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bla bla PO12458 bla bla[/TD]
[TD]12458[/TD]
[TD][/TD]
[TD][/TD]
[TD]123821[/TD]
[TD]123821[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]bla bla bla POS-12345[/TD]
[TD]PO-12345[/TD]
[TD][/TD]
[TD][/TD]
[TD]PO-124591[/TD]
[TD]124591[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]bla bla bla bla PO-99999[/TD]
[TD]Not found[/TD]
[TD][/TD]
[TD][/TD]
[TD]PO-12345[/TD]
[TD]12345[/TD]
[/TR]
</tbody>[/TABLE]

 

[NickvdB]

Ok works very well, thank you so much.
Two follow up questions:
1) What will happen if in column A on different rows the following two descriptions are used "PO12345" and "PO-12345". What will happen? Will only the first one will be found or will both be found?
2) Is there a possibility to get an error when two numbers are used in one Description?? "PO12345 / PO12458" will result in "Please check"

 

[admiral100]

1. Yes only the first one will be found

Regarding 2 you need one more help column :

F2 =SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
G2 =LOOKUP(9^9,0+MID(E2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},E2&1/19)),ROW($1:$19)))
B2 =IFERROR(INDEX($E$2:$E$5,MATCH(F2,$G$2:$G$5,0)),IF(LEN(F2)>7,"Please check","Not found"))


[TABLE="width: 763"]
<colgroup><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Description[/TD]
[TD]Desired result[/TD]
[TD][/TD]
[TD][/TD]
[TD]Table to compare[/TD]
[TD]Help1[/TD]
[TD]Help2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PO12345 / PO12458[/TD]
[TD]Please check[/TD]
[TD][/TD]
[TD][/TD]
[TD]12458[/TD]
[TD]1234512458[/TD]
[TD]12458[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bla bla PO12458 bla bla[/TD]
[TD]12458[/TD]
[TD][/TD]
[TD][/TD]
[TD]123821[/TD]
[TD]12458[/TD]
[TD]123821[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]bla bla bla POS-12345[/TD]
[TD]PO-12345[/TD]
[TD][/TD]
[TD][/TD]
[TD]POI-124591[/TD]
[TD]12345[/TD]
[TD]124591[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]bla bla bla bla PO-99999[/TD]
[TD]Not found[/TD]
[TD][/TD]
[TD][/TD]
[TD]PO-12345[/TD]
[TD]99999[/TD]
[TD]12345[/TD]
[/TR]
</tbody>[/TABLE]

 

[NickvdB]

OK, I think this will get too complex , especially when sometimes an invoicenumber will be entered in the same text. But thank you so much!