Formula Help for a newbie please!

[bekijane81]

Hi All,

Apologies this is my first post so I hope I get this right!

Its been a while since I've worked on excel formulas and I am a little rusty to say the least.

Basically I have one workbook with three sheets.

Sheet 1 has names across the top and subjects down the side, the cells which have been marked with the grades A, B, or C

Sheet 2 has the subject down the side, then across the top the headings A, B or C. in the cells under each there is recommendation text relating to the grade

Basically I want cells in sheet 3 to read the text from sheet 2, based upon the mark given

So if you have been given a Grade A in Sheet one, the recommendation is...


I hope this makes sense to someone? If anyone can give me any help it would be much appreciated

Bekijane

 

[elmer007]

If I understand your setup correctly, then I may have a solution. Here's my example/test setup:

Sheet1 has a table in A1:D4 as follows:

[blank]	Albert	Betty	Carlos
Math	A	B	C
Science	C	A	B
Reading	B	C	A

<tbody>
</tbody>

Sheet2 has a table as follows:

[blank]	A	B	C
Math	Equality!	Time to add	Time to add more
Science	Laws!	Time to research	Time to research more
Reading	Words!	Time to read	Time to read more

<tbody>
</tbody>

Sheet3 is where I have a formula that says "Find the students grade from Sheet1 and show the corresponding information for a grade in that subject from Sheet2" which gives the following result:

[blank]	Albert	Betty	Carlos
Math	Equality!	Time to add	Time to add more
Science	Time to research more	Laws!	Time to research
Reading	Time to read	Time to read more	Words!

<tbody>
</tbody>

The results could be tweaked to also show what the grade was in addition to the suggestions (e.g., "B: Time to add").
The formula in cell B2 (Albert's math suggestion) is: =HLOOKUP(VLOOKUP($A2,Sheet1!$A$1:$D$4,MATCH(B$1,Sheet1!$A$1:$D$1,0),FALSE),Sheet2!$A$1:$D$4,MATCH($A2,Sheet2!$A$1:$A$4,0),FALSE) which can be copied into the rest of the table.

The ranges will need to be adjusted to fit your data tables. This could be slightly tricky if you're not familiar with the type of formula shown.

Let me know if this doesn't work or if it's not what you're looking for.

 

[bekijane81]

Thank you elmer so much, What a star! Amazing formula!
I am certain that this is the correct way, I am just having a little trouble on getting it to work on my spreadsheet (its pretty big!) and my sheets run a little differently.

Sheet 1 runs from B3:50CD

Names down the side and several subjects across the top (I am sorry I got this the wrong way around!)

Sheet 2 runs from A6:E88

This time the subject runs down the side, grades across the top, and the text comments is in each corresponding box. (like your example.)

Sheet 3 is a list rather than table ( I don't think this matters?) basically each name will eventually have their own sheet with there comments on.

Again HUGE thank you for your help!! HUGELY APPRECIATED

 

[elmer007]

Here is the formula for when the names/subjects are flipped on Sheet1 (names going down):
=HLOOKUP(VLOOKUP(B$1,Sheet1!$A$1:$D$4,MATCH($A2,Sheet1!$A$1:$D$1,0),FALSE),Sheet2!$A$1:$D$4,MATCH($A2,Sheet2!$A$1:$A$4,0),FALSE)



To adjust for Sheet1 range of B3:CD50 and Sheet2 range of A6:E88, change to:
=HLOOKUP(VLOOKUP(B$1,Sheet1!$B$3:$CD$50,MATCH($A2,Sheet1!$B$3:$CD$3,0),FALSE),Sheet2!$A$6:$E$88,MATCH($A2,Sheet2!$A$6:$A$88,0),FALSE)

(I haven't tested this formula with the range changes)


I'm not sure I understand what you want your final layout of Sheet3 to be. We can tweak that with some more direction.

 

[bekijane81]

Thank you so much

it worked!!!