# Help with returning a value.

#### InfinityCola

##### New Member
Hey, I'm pretty much a beginner when it comes to some of the formulas in excel, I'm struggling with a simple problem on my page currently.

I'm building an excel for my students, I currently only have two sheets.

Students and Week 1.

On the students sheet in Column A1 is a list of all the students, Column B1 is Week 1, C1 is Week 2 etc.

on the Week 1 sheet, I have Column A1 a list of all the activities from A3 to A50.

in column B1 i have the blank cell where the students name will go once they have chosen the activity they wish to take part in.

Then whatever activity is listed in A1-50 will be put into a cell on the Students sheet under week 1 next to there name

i currently have a formula this =if('Week 1'!B3:I50="John Smith" so it will search all of the blocks for the name I place in the available spots, but now im stuck, trying to get the formula to post the activity from A1 into the students sheet next to the students name.

here is a screenshot of the excel to see if it helps, hopefully it makes sense, all help is appreciated!

Thank you so much

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Fluff

##### MrExcel MVP, Moderator
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

#### InfinityCola

##### New Member
Ah thank you, Im using 365

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
=CONCAT(IF('Week 1'!\$B\$3:\$I\$50=A2,'Week 1'!\$A\$3:\$A\$50,""))

#### InfinityCola

##### New Member

Thank you for the quick response, so the way I envisioned it, would be that the formula locates the specific names that are put in the open spots, I currently have a list that is data validated of all of the students, I obviously cant post there names for security reasons, and used John Smith as an example.

So if you blocked John smith in one of the white open spots on "Beach" for example, Beach would be the word that appears next to his name under the week one column on the student list.

And i would repeat the formula for week 2,3,4 etc

Thanks again

#### Fluff

##### MrExcel MVP, Moderator
That's what the formula does, assuming that the students name is in A2 on the Students sheet.

#### InfinityCola

##### New Member

I'm just getting a blank space at the moment, i have placed the top student in the first placement

Thank you again!

#### Fluff

##### MrExcel MVP, Moderator
That looks to be in row 3 so you need to change the A2 to A3

#### InfinityCola

##### New Member
Wow. Thank you so much, I didn't see that, you've truly saved me a fair amount of time!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
2
Views
69
Replies
0
Views
72
Replies
9
Views
114
Replies
5
Views
113
Replies
5
Views
80

1,148,244
Messages
5,745,606
Members
423,964
Latest member
Rayds

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back