title: Trying to match two lines of text and return a third in excel
body: <p>I have a couple of different large lists of equipment on seperate worksheets I am trying to consolidate. The first sheet &quot;List&quot; contains multiple columns of information, including one for the area it will be relocated to. Another sheet, &quot;Locations,&quot; contains the three areas where each piece of equipment will be relocated to, seperated in to 3 columns; at the top of each column are the names of the locations 2500, 2900, and Term. The issue is, the Equip# from the &quot;List&quot; sheet only partially matches the names on the &quot;Locations&quot; sheet.</p>
  <p>List</p>
  <p><a href="https://i.stack.imgur.com/PqroN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PqroN.png" alt="" /></a></p>
  <p>Locations</p>
  <p><a href="https://i.stack.imgur.com/uIE7w.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uIE7w.png" alt="" /></a></p>
  <p>As you can see above, A2203-76 under Equip# only partially matches A2203-76 20% NAP TANK AGITATOR.</p>
  <p>This is the formula I am using {=INDEX(Locations!$A$2:$C$2,,MIN(IF(Locations!A3:C151=&quot;A2203-76&quot;,COLUMN(Locations!A:C),0)))}. This unfortunately will only return the first column value, which is 2500. What should happen is: First, on the List sheet, the New Gear Location cell should check the Equip# cell in the same row, after retrieving the Equip# it should go to the Locations sheet and search for a partial match to what it retrieved from the Equip# cell. After finding a partial match, it should look at the column the match was found in and return one of the 3 cell values from the top &quot;2500, 2900, or Term&quot; based on what column it was found in and fill this value in the cell on the List page that the formula was entered in to.</p>
  <p>Another issue, which makes me think this may not be possible, is there are also instances of A2203-75. So I question if excel has the ability to apply I guess &quot;weights&quot; to different partial matches. What I am getting at is, if I applied some function to partially match A2203-76 to something on the other page, would it just stop at A2203 and pick the A2203-75? Or would it look at the whole string first and find the correct instance? Maybe my thinking is wrong, but if you could offer any help I would appreciate it.</p>