Return value if scores contain value form min and Max column

0 votes

Formula to enter image description here

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 by Kithuzzz
• 38,010 points
336 views

1 answer to this question.

0 votes

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.


enter image description here


• 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()

enter image description here


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

answered Mar 20, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

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

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
277 views
0 votes
1 answer

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

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

answered Nov 6, 2022 in Others by narikkadan
• 63,420 points
1,177 views
0 votes
1 answer

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

answered Nov 11, 2022 in Others by narikkadan
• 63,420 points
1,001 views
0 votes
1 answer

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

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

answered Nov 24, 2022 in Others by narikkadan
• 63,420 points
430 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,232 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,418 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,670 points
696 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,325 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,420 points
551 views
0 votes
1 answer

Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,420 points
1,127 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP