Formula Help for a newbie please!

bekijane81

New Member
Joined
Jun 17, 2015
Messages
5
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

elmer007

Active Member
Joined
Aug 29, 2014
Messages
299
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

New Member
Joined
Jun 17, 2015
Messages
5
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

Active Member
Joined
Aug 29, 2014
Messages
299
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top