Help with simple nested IF formulas

**aisle5** · 11-14-2014, 09:01 PM

Hi,

I need help creating a formula, which:

Checks Column 1 to see if it's "Grade" or "Percentage", then checks Column 2 for "low", "med", "high".

If Column 1 is "Grade", then it should print the corresponding LowGrade, MedGrade, or HighGrade (identified in separate table).
If Column 1 is "Percentage", then it should print corresponding LowPer, MedPer, or HighPer (identified in separate table).

Could you guys help with this simple formula? It would be highly appreciated!

Let me know if I need to clarify, thanks,

John

**FDibbins** · 11-14-2014, 09:10 PM

Hi, welcome to the forum

Without seeing a sample of what you are working with, this is untested.

Could you change your answers to xxxGRAD and xxxPERC? That will make it a LOT simpler to just grab teh 1st 4 letters, instead of all letters for GRADE, but only the 1st 3 letters of PERCENT. BAsed on a "yes" answer here...

In the cell where you want the answer to be...
=B2&leftA2,4)

**aisle5** · 11-14-2014, 09:28 PM

Hi, thanks for responding.

I should specify that LowGrade, MedGrade, HighGrade and LowPer, MedPer, HighPer, aren't texts. They are named cells with numeric values.

My first table to scan is:

Column 1 Column 2
Grade Low
Grade Medium
Percentage Low

Then based on if it's Grade/Percentage, it will check column two for low/med/high and input the figure from the below table:

Grade Percentage
L 1 0.5
M 4 2
H 8 4

Sorry if my explanation is a bit muddled :/

**aisle5** · 11-15-2014, 02:26 PM

If anyone could help I would really appreciate it

**martindwilson** · 11-15-2014, 02:30 PM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**aisle5** · 11-15-2014, 03:09 PM

I've attached a sample of the problem I'm trying to solve.

Data in Column 1 and Column 2 are pre-populated, as in the data in A3:C6.

I'm trying to come up with a formula to calculate the data in Column 3 (G4:G9)

Example.xlsx

**martindwilson** · 11-15-2014, 03:33 PM

g4
=INDEX($B$4:$C$6,MATCH(F4,$A$4:$A$6,0),MATCH(E4&"*",$B$3:$C$3,0))
then conditional format g4:g9
as formula
=$E4="percentage"
and choose percentage from the number format option
not e4&"*" is used as "grade" <>"grades"