CODEWITHSUNDEEP
excelexcel-formula
Foxtrail815
Foxtrail815

Reputation: 53

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

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.

enter image description here

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

enter image description here

Upvotes: 0

Views: 119

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

Use INDEX/MATCH:

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

enter image description here

Upvotes: 1

Related Questions