CODEWITHSUNDEEP
excelexcel-formula
SchokoBons
SchokoBons

Reputation: 3

Give the different row value when text string found in another

Having issues with merging some IFs and VLOOKUPs most probably. Basically, workflow would look like below and I don't quite know how to type it into one formula.

  1. Look for value from Values to look for in the table on the right hand side.
  2. If found in the list text string, return the corresponding value from add/remove column to B column.

Excel screenshot sample

Upvotes: 0

Views: 59

Answers (2)

Sikhumbuzo Nzimande
Sikhumbuzo Nzimande

Reputation: 13

try this formula: =IF(IFERROR(SEARCH(A2,E2),"Not Found")="Not Found","Not Found",D2)

also please see the screenshot attached Example

Upvotes: 0

MGP
MGP

Reputation: 2551

The answer will only work if you make the name for the values unique. What I mean by this is, that you will need to change the name of value1 to value01 if you have more than 10 values. If you have more than 100 values, than you will need to change the name to value001 and so on.

Then use this in B2 and drag down as needed:

{=INDEX($D$3:$D$6,MATCH(TRUE,FIND(A2,$E$3:$E$6)>0,0))}

NOTE: you dont have to entere the brackets {}. These just indicate that this is an array-formula. This needs to be entered with CTRL + SHIFT + ENTER instead of the normal ENTER.

Upvotes: 1

Related Questions