CODEWITHSUNDEEP
excel
Guillaume
Guillaume

Reputation: 39

Search a specific text within a cell and return a matched text from a list

I cannot figure out how to make the below formula work - it always results in an #N/A. I'm trying to search in a cell for certain text from a list and return when a match is found another text from a list.

!(https://photos.google.com/photo/AF1QipMrqey3My-qDeMlFSWoNmvyp39lo8H5Q8Fl_k4)

I tried vlookup, and want to avoid the usage of if (the complete list is about 40+ countries) but cannot make it work a index, search and isnumber.

My end result would be the following : search the cell "Singapore FSS", have the formula recognize "Singapore" and return the value of "ASE"

=INDEX(D1:E4,MATCH(TRUE,ISNUMBER(SEARCH(A2,D1:D4)),0),MATCH("text to return",D1:E1,0))

Thank you in advance for your help and comments

Upvotes: 0

Views: 1226

Answers (1)

Viacheslav Zhukov
Viacheslav Zhukov

Reputation: 1360

Ok, here's the logic:

  1. We have cells to be processed and a table with cities/codes to be found and return

  2. Build an array of indices of found cities using SEARCH function (this array will contain either index or #VALUE! if city isn't found)

  3. Find the position of a non-error element (Ok, I consider, that there's only one match, so we return the first one) using MATCH function

  4. Find the value to return using obtained relative position

Here's my sample data:

Sample data

Here's the function. It should be array-formula to properly build array of indices (create it for the first cell using Ctrl-Shift-Enter)

{=INDEX($E$1:$E$4;MATCH(1;SEARCH($D$1:$D$4;A1);-1))}

Then simply drag it, or use Excel Tables.

The result:

enter image description here

Upvotes: 1

Related Questions