Formula Help for a newbie please!

bekijane81

New Member
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

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

elmer007

Active Member
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
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
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

New Member
Thank you so much

it worked!!!

Replies
0
Views
65
Replies
5
Views
259
Replies
5
Views
88
Replies
13
Views
452
Replies
1
Views
158

1,171,893
Messages
5,878,068
Members
433,315
Latest member
KXZ

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.

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