Formula to

If my score in column A is between the minimum and maximum marks in columns d and e, I want to put a formula in box 4 to determine my grade. For clarity, please see the attached photo.

Mar 20, 2023 in Others 423 views

So based on my comments above, here are the screenshots where the formula is working. Since I am using MS365 therefore I don't need to hit CTRL+SHIFT+ENTER when exiting edit mode for the second alternative, but for those using earlier versions needs to hit.

• Formula used in cell B4

```=XLOOKUP(1,(A4>=Min_Marks)*(A4<=Max_Marks),Grade,"")
```

Where: Min_Marks, Max_Marks & Grade are defined names for the ranges, --> =\$D\$4:\$D\$13, =\$E\$4:\$E\$13, =\$F\$4:\$F\$13 you can also use without using defined names.

```=XLOOKUP(1,(A4>=D\$4:D\$13)*(A4<=E\$4:E\$13),\$F\$4:\$F\$13,"")
```

Alternatively if you don't have access to XLOOKUP() then use INDEX() with MATCH()

• Formula used in cell B4

```=INDEX(Grade,MATCH(1,(A4>=Min_Marks)*(A4<=Max_Marks),0))
```

Or, without defined names:

```=INDEX(F\$4:F\$13,MATCH(1,(A4>=D\$4:D\$13)*(A4<=E\$4:E\$13),0))
```

Note: Remember to hit CTRL+SHIFT+ENTER while exiting the edit mode for the second formula.

• 63,720 points

Check multiple cells in excel, move to next cell if null and if value found stop and return that value

Use this formula: =INDEX(FILTER(A2:D2,A2:D2<>""),1,1) INDEX returns the first value ...READ MORE

Excel delete row if column contains value from to-remove-list

Given sheet 2: ColumnA ------- apple orange You can flag the rows ...READ MORE

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

Prompt message if Excel column has some value in asp.net c#

I believe it is wise to use ...READ MORE

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE