RJB78

Board Regular
Joined
Aug 8, 2016
Messages
69
Office Version
  1. 365
Hello. I am trying to get A,B,C,D to appear based on a different cells value

IF U7 is less than 5; then "A"
IF U7 is less greater than 5, but less than 20; "B"
IF U7 is greater than 20; then "C"
IF U7 is "N/A" (from a vlookup); "D"

This is what I have now and it works for the "C" but returns #Value for anything that should return "A" and False for anything that should return "B"

=IF(IF($U7<5,"A"),IF(AND($U7>5,$U7<20),"B"),IF($U7>20,"C"),IF($U7="N/A","D"))
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try this:

Code:
=IF($U7<5,"A",IF(AND($U7>5,$U7<20),"B",IF($U7>20,"C",IF(ISERROR($U7),"D"))))
 
Last edited:
Upvote 0
That works except the last part, "C" appeared instead of "D"
 
Last edited:
Upvote 0
what do you have in U7?

Also, please note that you don't have criteria stating what you want if something is exactly 5 or 20, you only have greater than or less than, not greater than or equal to or less than or equal too. If you want to include 5 and 20, try the below.

Code:
A=IFERROR(IF($U7<5,"A",IF(AND($U7>=5,$U7<=20),"B",IF($U7>20,"C"))),"D")
 
Last edited:
Upvote 0
U7 is a vlookup to a number, the letter is based off the number, but for some lookups there aren't any numbers so it returns N/A, I want it to return D
 
Upvote 0
Does the below formula not work for you? It should.

Code:
=IFERROR(IF($U7<5,"A",IF(AND($U7>=5,$U7<=20),"B",IF($U7>20,"C"))),"D")
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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?

Disable AdBlock

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
Back
Top