Extract value if first characters match text in a range of cells-CodePudding

Home > OS > Extract value if first characters match text in a range of cells

Extract value if first characters match text in a range of cells

Time：06-24

I have a table with the following formula:

=IF(SUMPRODUCT(-- (LEFT(A1:D1,5) = "__FTC")),"Exists", "No Exist")

It successfully detects if the first 5 characters contain the word "__FTC" as shown on column E.

I need to get the value instead of just detecting if the value exists as shown below:

CodePudding user response：

Use INDEX/MATCH:

=IFERROR(INDEX(A1:D1,MATCH("__FTC*",A1:D1,0)),"")