CODEWITHSUNDEEP
excellistindexingmatch
John Kenny
John Kenny

Reputation: 13

Index Match -Return match based on list of possible strings

I have a data set that looks something like this

Hostname:      Abr: Descr:
123-svr-xyz    svr  Server
456-rtr-wxy    rtr  Router

I would like to do a string search of the Hostname field against the list of values in the Abr field, and return the corresponding Description from the Descr field.

I understand how index match formulas work, but I can't quite figure out how to reference a list in this way.

Any suggestions would be appreciated.

Upvotes: 0

Views: 46

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

If the part that is being found is always the second part and deliniated with "-" then you can use this formula:

=INDEX(F:F,MATCH(MID(A2,FIND("-",A2)+1,FIND("-",SUBSTITUTE(A2,MID(A2,FIND("-",A2)+1,999),""))-1),E:E,0))

enter image description here

If not then you will need to use this array formula:

=INDEX($F$2:$F$3,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$3,A2)),0))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly Excel will put {} around the formula.

enter image description here

Upvotes: 1

Related Questions