-3

I have two separate tables in an Excel worksheet, as illustrated below.  Columns A, B, E, F and G are my input values; I want a formula that generates the values in Column C (shown in italics):

  A B C D E F G
 1 Name Value Type Type Lower Upper
 2 Andy 35 Spam Spam 35 39
 3 Mark 85 Foo Ham 25 27
 4 Pat 28 N/A Eggs 91 95
 5 Deb 93 Eggs Foo 82 86
 6 Emily 92 Eggs Bar 65 69
 7 Greg 22 N/A
 8 Gary 67 Bar

For each row in the first table (i.e., each person), I want to find that person’s Type based on their Value (Column B) by finding the row in the second table where the person’s Value falls within the Lower→Upper range (Columns F and G).  So, for example, Mark (Row 3) has a Type of “Foo” because his Value (85) falls between 82 and 86, associated with “Foo” in Row 5 of the second table.  Note that Pat’s Value is 28 (Row 4), which does not match any range.

The ranges are inclusive.  For example, Andy’s Type is “Spam” (Row 2) even though his Value (35) equals the Lower end of the range for “Spam” (Cell F2).

I know nested IFs are quite hard to debug in Excel, so I’d like to avoid that if possible.

mr NAE
  • 3,144
  • 1
  • 15
  • 35
sudden_clarity_clarence
  • 303
  • 4
  • 10
  • Not sure if that works, because it doesn't make use of the ranges. For example, an A grade requires your mark to be between 80 - 100. – sudden_clarity_clarence Dec 16 '20 at 20:26
  • Well the issue with relying on the third parameter is that anything over the highest value in column F would return N/A. Further to that, and maybe its my fault for not clarifying, the ranges in my actual problem are not clean and sorted like this example. Values between 2 to 5 is assigned to one category, then values between 63 to 70 could be assigned another category with nothing in between both ranges. – sudden_clarity_clarence Dec 16 '20 at 20:34
  • You're probably right – sudden_clarity_clarence Dec 16 '20 at 20:36
  • Update your question to better reflect the problem you are having. Also, does the VLOOKUP solution below not work for you? – urdearboy Dec 16 '20 at 20:37
  • 1
    @sudden as always, you shoud add to your Q actual representitive data (as text, not as an image) redacted if necassary to remove sensitive information, and examples of expected results. – chris neilsen Dec 16 '20 at 20:41
  • @BigBen I updated my question to really reflect the issue I'm having, as well as the output I'm expecting. Hope you had time to take a second look, thanks – sudden_clarity_clarence Dec 16 '20 at 21:03
  • Voted to reopen based on your latest edit. Though the value for `28` should probably be `N/A`, not `Ham`. – BigBen Dec 16 '20 at 21:05
  • You're right, sorry I really need to QC my examples before pasting it.. – sudden_clarity_clarence Dec 16 '20 at 21:12
  • You could use `=INDEX($F$2:$F$6,MATCH(1,($G$2:$G$6<=B2)*($H$2:$H$6>=B2),0),)`. Depending on your version of Excel may need confirmed with Ctrl+Shift+Enter. Or if you have access to the FILTER function, `=IFERROR(FILTER($F$2:$F$6,($G$2:$G$6<=B2)*($H$2:$H$6>=B2)),NA())`. – BigBen Dec 16 '20 at 21:15
  • Yup, that works. Tried it on my massive dataset and its referencing the ranges and labelling correctly. I'm going to have to spend some time deciphering your formula to understand it, but this is the right answer. I assume the question needs to be re-opened to mark it as such. – sudden_clarity_clarence Dec 16 '20 at 21:31
  • re-opened @BigBen – Scott Holtzman Dec 16 '20 at 21:39
  • 1
    @sudden_clarity_clarence it seems you got your answer. But in future when you post sample data, post it **as text** (if you feel an image adds value you can post that too, but always include it as text). Making people retype _your_ data is not OK – chris neilsen Dec 16 '20 at 21:50

2 Answers2

0

Modify your source table to show the lower limit for each grade and then use a VLOOKUP with a approximate match

Update to show how to include blank ranges

enter image description here

Filter Solution

enter image description here

urdearboy
  • 14,439
  • 5
  • 28
  • 58
0

If you don't have access to the FILTER function, one option is an INDEX/MATCH array formula, which will need confirmed with Ctrl+Shift+Enter.

=INDEX($F$2:$F$6,MATCH(1,($G$2:$G$6<=B2)*($H$2:$H$6>=B2),0),)

enter image description here

Another option is LOOKUP, which does not need Ctrl+Shift+Enter.

=LOOKUP(1,1/(($G$2:$G$6<=B2)*($H$2:$H$6>=B2)),$F$2:$F$6)

enter image description here

BigBen
  • 46,229
  • 7
  • 24
  • 40