Find String in one Column with Condition in Another Column

[Gos-C]

Hi all,

When I used the following formula:

it returns 1. However, I need row 3 – the one with the smallest number in column A. Any help?

Thanks,
Gos-C

[6StringJazzer]

The content of column A is not a number, it's text. How do you define "smallest"? From a text point of view your example is sorted in lowest-to-highest order, so row 1 does have the smallest value.

[Bernie Deitrick]

IF by smallest you mean the value of the third set of digits, then with your table in columns A to C, in D2 enter the formula

=VALUE(MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,LEN(A2)))

Copy that down to match your list. Then use this array-entered formula, entered using Ctrl-Shift-Enter:

=SUM(IF(NOT(ISERROR(FIND("ACETYL-L-CYSTEINE",C2:C1000))),IF(D2:D1000=MIN(IFERROR(VALUE(MID(A2:A1000,FIND("-",A2:A1000,FIND("-",A2:A1000)+1)+1,LEN(A2:A1000))),100000)),ROW(A2:D1000))))

This assumes that the combinations of numbers and names are unique....

[Gos-C]

Thank you very much for your replies. Yes, Bernie's assumption is correct, and his formulas worked perfectly on the sample table that I provided. However, I can't figure out why the 2nd and main formula is not working on my full table (which has 3296 rows), even though I changed the ranges last row in the formula to 3296 and entered using Ctrl-Shift-Enter. It is returning 0.

[Bernie Deitrick]

Works for me - here is my test workbook. Make sure that your data has no dupes, errors, or blanks. If you could upload a version that shows that behavior

[Gos-C]

Hi Bernie,

Please find the file attached.

[Bernie Deitrick]

Array enter

=SUM(IF(NOT(ISERROR(FIND("ACETYL-L-CYSTEINE",C2:C3296))),IF(D2:D3296=MIN(IF(NOT(ISERROR(FIND("ACETYL-L-CYSTEINE",C2:C3296))),IFERROR(VALUE(MID(A2:A3296,FIND("-",A2:A3296,FIND("-",A2:A3296)+1)+1,LEN(A2:A3296))),100000))),ROW(A2:D3296))))

[Gos-C]

Hi Bernie,

Thanks for the modified formula. I really appreciate your help. There were still some issues with it though; It was returning some weird results (the row number was outside the data range) for some search items. I figured it out: it was because of seemingly duplicates. So I concatenated C with its description from another column and used it instead of C.

Also, I need to modify the formula to extract the unit size (i.e., 250, 120,500 etc.) as well, from the following:

[HTML][/55-2303-250 MG
30-4399-U120
55-4475-500 MG
23-1012-100 MG
24-2986-100 MG
55-2627-500 MG
30-1419-500 MG
55-1714-500 MG
20-1019-100 MG
20-1019-500 MG
55-1794-500 MG
30-1705-200 MG
30-2185-250 MG
55-2692-1080 MG
55-2692-540 MG
55-1877-250 MG
30-2963-500 MG
50-3512-500 MG
20-3213-10 MG
20-3213-100 MG
35-1576-25B
35-1576-25P
35-2127-25B
35-2127-25P
HTML]

Thanks

[Bo_Ry]

For extract the unit size, Data in column A
B2 copy down

=IFERROR(LOOKUP(9^9,--MID(A2,9,{1,2,3,4,5,6})),LOOKUP(9^9,--RIGHT(MID(A2,9,5),{1,2,3,4,5})))

[Gos-C]

Great! Thank you, thank you, thank you very much, Bo_Ry.