Return Specific Value from adjacent cell if values match

  • Hello!I am going to try and explain this as well as I can. I have a listing of several hundred different locations in one table. In another I have a listing of all of the technicians that service those locations next to the location that they service. I need a formula that will check the location value of each cellof my larger table, and search the second table for the matching value and then output the technician associated with that value.As an example I have 5 sites under the location column that equal Oshkosh, WI in the first table. On the second table there are two columns, technician and location. The cell that equals Oshkosh, WI in the second table is directly across from the cell says 'John Smith'. Therefore John Smith should be output in the technician column across from all of the rows in table 1 that have Oshkosh, WI in the location column.I hope that makes sense, I tried to include a screenshot but my company is blocking it..Any help will be greatly appreciated! Thank you!

  • Re: Return Specific Value from adjacent cell if values match


    I would create a small sample sheet, dummy data is fine, and upload it so we can see your data and also so you can understand where the solution is going and what it references.


    When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

    Bruce :cool:

  • Re: Return Specific Value from adjacent cell if values match


    Here is an example of (a very small segment) of what I am trying to do. I had to switch browsers to get this to upload, so hopefully it displays ok.

    Files

    • sample.xlsx

      (9.38 kB, downloaded 87 times, last: )
  • Re: Return Specific Value from adjacent cell if values match


    So in E2 of your sample you would enter the formula below and then copy and paste it into E3 to E10.

    Code
    1. =INDEX($G$2:$G$6,MATCH($B2,$H$2:$H$6,0))

    Bruce :cool:

  • Re: Return Specific Value from adjacent cell if values match


    Thank you so much you are a life saver! I was able to tweak that for my actual reports and it worked!! I have a presentation tomorrow and I was freaking out thinking I was going to have to stay up all night and do this manually. Thank you!!! :cheers: