Excel - lookup multiple values in a string and return first one

1
votes

I need a function to look up organizational abbreviations in a text and return the first one thas shows up. I tried to solve that with a nested if clause but it has a logical error.

=IF(ISNUMBER(SEARCH("BZC";I5)); "Finanz"; IF(ISNUMBER(SEARCH("AZC" /1";I5));"IT";""))

It looks up BZC and AZC as desired and return the organization name. However, it does no return the first match in a string. Since BZC is the first lookup it will always be returned if it is in the string, eventhoug it might not be the first org abbreviation.

What functionality of excel can be used to solve this issue? I basically need an array of variables that a function should do a look up and return the first one that is found.

Edit:

I tried to implement the formula form Justyna MK. Besides the fact, that I still need to figure out the meaning of the formula (iferror, mid, small) It returns #N/A in my example. #N/A Is there a certain reason for that?

1
excelnestedlookup
Post some sample data and desired result based on that sample. – basic
Hi Julian, as described in my edited post, try the same formula but with , instead of ; inside curly brackets. Also you entered this formula without using Ctrl+Shift+Enter (the first formula I posted is an Array formula). – Justyna MK

1 Answers

4
votes

I hope I understood your request correctly. Here's an Array formula for you to try (enter using Ctrl + Shift + Enter):

=CHOOSE(MATCH(MID(A1,SMALL(IFERROR(SEARCH({"BZC","AZD","xxx"},A1),""),1),3),{"BZC","AZD","xxx"},0),"Finanz","IT","Other")

You'd probably need to change , to ; in order to match your regional settings.

You can easily expand the list of search items by modifying the contents of curly brackets { } and also by expanding the MATCH results at the very end of the formula.

Here's some sample result:

enter image description here

Edit: here's an adjusted solution that ignores the length of your code (the previous solution was assuming that the code is always 3-characters long). This time it's not an array formula so you can enter it as it is. Also, I suspect that you should not change , to ; inside the curly brackets (it would modify the formula from column to row delimiter and thus it will stop working). The remaining , can be transformed to ;, if that makes sense.

=CHOOSE(MATCH(TRIM(LEFT(SUBSTITUTE(RIGHT(A1,LEN(A1)-SUMPRODUCT(SMALL(IFERROR(SEARCH({"BZC","AZD","XYZX/1","NP-HSD"},A1),""),1))+1)," ",REPT(" ",255)),255)),{"BZC","AZD","XYZX/1","NP-HSD"},0),"Finanz","IT","Other1","Other2")

The result:

enter image description here