Comparing in Excel

[tguidry]

Can I compare a range value like 33-54 to a value like 47 and come back with answer of yes or no? I have a vertical look up formula returning a range of values that I would like to compare to another value and state whether that student has met their goal or not. If the student is within the range then Yes would appear if not then No would appear. Since the range is an answer from a vertical look up table, several different ranges can come up depending on what is typed. I want to know if I can create a formula that will compare whatever range appears to the actual score and tell if it is within the range or not

 

[Aladin Akyurek]

Care to post a small sample?

 

[Gali]

vlookup with True. Set up a table with criterea you want to return (In your example it could look something like below). Then do a vlookup of the first column, returning column 3 value, with TRUE (approximate match). Note that table created must be sorted in numeric order.

[TABLE="width: 272"]
<tbody>[TR]
[TD] From [/TD]
[TD] To [/TD]
[TD] Remark[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]33[/TD]
[TD] Poor[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD="align: right"]50[/TD]
[TD] Below average[/TD]
[/TR]
[TR]
[TD="align: right"]51[/TD]
[TD="align: right"]75[/TD]
[TD] Meeting expectations[/TD]
[/TR]
[TR]
[TD="align: right"]76[/TD]
[TD="align: right"]100[/TD]
[TD] Excellent



[/TD]
[/TR]
</tbody><colgroup><col span="2"><col></colgroup>[/TABLE]

 

[tguidry]

ADV 47-57
H-AB 19-22
H-BAS 27-32
H-MAS 39-46
H-UNS 12-14
L-AB 15-18
L-BAS 23-26
L-MAS 33-38
UNS 0-11
This is my vlookup. A project goal is put in the first column L-MAS, the second column returns 33-38, the third column is a sum of total points earned 30, the fourth column is Y or N have they met their goal. I would like to be able to compare the range of numbers in column 2 to the number in column 3.

 

[FormR]

Hi, something like this maybe:

Excel 2013/2016
	A	B	C	D
1	ADV	47-57	30	N
2	H-AB	19-22	19	Y
3	H-BAS	27-32	32	Y
4	H-MAS	39-46	40	Y
5	H-UNS	12-14	5	N
6	L-AB	15-18	50	N
7	L-BAS	23-26	1	N
8	L-MAS	33-38	55	N
9	UNS	0-11	5	Y
Sheet2

Cell Formulas
Range	Formula
D1	=IF(AND(C1>=0+LEFT(B1,FIND("-",B1)-1),C1<=0+MID(B1,FIND("-",B1)+1,13)),"Y","N")

 

[tguidry]

Hi, something like this maybe:

Excel 2013/2016

	A	B	C	D
ADV	47-57
H-AB	19-22	Y
H-BAS	27-32	Y
H-MAS	39-46	Y
H-UNS	12-14	N
L-AB	15-18	N
L-BAS	23-26	N
L-MAS	33-38	N
UNS	0-11	Y

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]30[/TD]
[TD="bgcolor: #FFFF00"]N[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]19[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]32[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]40[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]50[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]55[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=IF(AND(C1>=0+LEFT(B1,FIND("-",B1)-1),C1<=0+MID(B1,FIND("-",B1)+1,13)),"Y","N")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This is almost it; however, I would like it to say "Y" for D6 and D8 if they exceeded their goals.

 

[Aladin Akyurek]

This is almost it; however, I would like it to say "Y" for D6 and D8 if they exceeded their goals.

In D1 enter and copy down:

=ISNUMBER(LOOKUP(C1,CHOOSE({1,2,3},LEFT(B1,FIND("-",B1)-1)+0,REPLACE(B1,1,FIND("-",B1),"")+0,9.99E+307)))+0

A value of 1 means a hit, 0 miss.

 

[tguidry]

Thank you so much! I just changed the < to a > sign and it works. If you could be so kind as to explain this formula in layman's terms so I can learn from this, that would be great. Why do they have 0+Left and 0+mid? why do you need the -1 and +1? What's up with the 13? Sorry, just trying to learn.

 

[Aladin Akyurek]

In D1 enter and copy down:

=ISNUMBER(LOOKUP(C1,CHOOSE({1,2,3},LEFT(B1,FIND("-",B1)-1)+0,REPLACE(B1,1,FIND("-",B1),"")+0,9.99E+307)))+0

A value of 1 means a hit, 0 miss.

Edit... The formula should be:

=ISNUMBER(LOOKUP(C1,CHOOSE({1,2},LEFT(B1,FIND("-",B1)-1)+0,REPLACE(B1,1,FIND("-",B1),""))))+0

 

[FormR]

I would like it to say "Y" for D6 and D8 if they exceeded their goals.

Hi, just another option you can try:

Excel 2013/2016
	A	B	C	D
1	ADV	47-57	30	N
2	H-AB	19-22	19	Y
3	H-BAS	27-32	32	Y
4	H-MAS	39-46	40	Y
5	H-UNS	12-14	5	N
6	L-AB	15-18	50	Y
7	L-BAS	23-26	1	N
8	L-MAS	33-38	55	Y
9	UNS	0-11	5	Y
Sheet1

Cell Formulas
Range	Formula
D1	=IF(C1>=0+LEFT(B1,FIND("-",B1&"-")-1),"Y","N")