# 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

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
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.

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

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.

Thank you so much

it worked!!!

Replies
10
Views
134
Replies
0
Views
89
Replies
0
Views
353
Replies
4
Views
132
Replies
6
Views
487

1,214,438
Messages
6,119,523
Members
448,904
Latest member
uNiek

### 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?

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