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 in Others 74 views

## 1 answer to this question.

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,040 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