Find and match names on a different sheet and then copy the adjacent cells

[windrain]

Good morning everyone!
I have a workbook with 2 sheets. Current Tab is where I need the formula. Change things here Sheet is where to update information.

I have 3 locations in my sample. On the 'Current Tab' I am needing a formula for columns F - S, rows 10-13, rows 18-21 and rows 26 - 29. I have outlined these in red. Hopefully to make this easier. All of the blue cells should have an X if the formula is working. The other cells within the Red outlines should be blank.
The information is to come from Sheet 'change things here K3-X8 in my sample.
So for the formula I want to find the name on 'change things here J3:J8 that matches Current Tab B10:B29 and then populate Current Tab F - S, rows 10-13, rows 18-21 and rows 26 - 29, with what is adjacent to 'change things here J3:J8 in columns K-X
'Current Tab' F10:S11 have formulas I have tried but obviously don't work!

In short we have 50+ people that occasional change locations and when I make changes on 'change things here' B4:H6 I need the characteristics to follow the names.

I really hope this makes since. Please let me know if you need more clarification!
Thank you in advance all who look at my question!!

[Pete_UK]

You can use this formula in K10 of the Current sheet:

=IFERROR(INDEX('change things here'!K$3:K$8,MATCH($B10,'change things here'!$J$3:$J$8,0))&"","")

You can copy this across and down the top block (probably better to use Copy then Paste Special | Formulas | OK, so as to not mess up your formatting). You can also copy the formula into the lower blocks, and the cell references will automatically adjust.

Hope this helps.

Pete

[windrain]

This works perfectly! Thank you so much!
I was wondering, I have seen this & used in alot of formulas, but I am not sure what it is used for and when to use it??

[Pete_UK]

The ampersand symbol, & , is used to join text together. It can be used instead of the CONCATENATE function, and is obviously much shorter to type. In this particular case, if the cell from which the data is to be returned is empty, then Excel will treat this as zero, but by joining on a null string, "", then this will also be converted to a null string and the receiving cell will also appear empty. It's a shorter way than doing:

=IF(long_formula="","",long_formula)

Hope this helps.

Pete

P.S. Thanks for the rep, and if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.