multiple conditions for IF statement in a table, can Excel handle this kind of processing?

[Neo4u]

Hello All,

I am having another problem, I have the following situation.. I want to assign a value from a Table where the specific condition is given in the table itself.

the problem here is i got multiple conditions before he can assign the right value and the problem is the list is for about 200 entries i want to assign a score to. And i want to use about 37 criteria's like so to do this manually it will take a tremendous time and this must be anti-excel way of working!!
0<x< 10 then assign A
10<x<20 then assign B
etc etc..
Thank you.

 

[vds1]

Your table should be like

0 to 9 -A

and not

0- 10 - A

Because if i get score 10, grade would be B and not A.

Correct me if i am wrong ?

 

[Aladin Akyurek]

Given a table like in D:F:

			Level-1	Level-2	Score
0	A		0	9	A
3	A		10	19	B
19	B		20	29	C
21	C		30	39	D
12	B		40	49	E
70	H		50	59	F
75	H		60	69	G
10	B		70	79	H
			80	89	I
			90	99	J
			100	109	K
			110	119	L
			120	129	M
			130	139	N
			140	149	O
			150	159	P
			160	169	Q
			170	179	R
			180	189	S
			190	199	T
			200	209	U
			210	219	V
			220	229	W
			230	239	X
			240	249	Y
			250	259	Z

<colgroup><col style="width: 48pt;" span="6" width="64"> <tbody>
</tbody>

B2, copy down:

Either...

=LOOKUP(A2,$D$2:$D$27,$F$2:$F$27)

Or...

=VLOOKUP(A2,$D$2:$F$27,3,1)

 

[Neo4u]

no , you are right, but i think you can correct it with the following mathematical operation:

x <x<=10
greater than <x<=20
0 or less or equal to 10 then assign A
<x<=z
x greater than 10 or less or equal to 20 then Assign B
etc..
etc..
(he doesn't DISPLAY the greater than or less than simple that is why i am writing it fully out)

But that is not the real problem, i can work around that or adjust that , but my main point is how can you make a filter or a lookup that can handle such multiple criteria.. because i am not planning to put all the criteria 37 by hand, if you understand what i mean..</x<=z
</x<=20
</x<=10

 

[Aladin Akyurek]

no , you are right, but i think you can correct it with the following mathematical operation:

x <x<=10
greater than <x<=20
0 or less or equal to 10 then assign A
<x<=z
x greater than 10 or less or equal to 20 then Assign B
etc..
etc..
(he doesn't DISPLAY the greater than or less than simple that is why i am writing it fully out)

But that is not the real problem, i can work around that or adjust that , but my main point is how can you make a filter or a lookup that can handle such multiple criteria.. because i am not planning to put all the criteria 37 by hand, if you understand what i mean..</x<=z

See Post #3...</x<=20
</x<=10

 

[Neo4u]

oke here is the real file, maybe this will help to better understand, i just tried your solution but i am not sure if that worked for me, see example here below.

oke the meaning is that if member hhhh is level so he can use a AK-47 otherwise the best other weapon he can use is from level from level 5 Tommy gun.
member rich is level 15 he should use the stick grenade (level 14) he cannot use the level 17 RPG-7 and nor the chaingun (level 12).
So i want to assign the highest possible weapon Item corresponding to his level :D
oke i hope you get the clue what i want to achieve..

 

[Neo4u]

Hey, your first formula worked!!!! ****zz!! you are great man!
It was indeed the Lookup function...I only knew the vlookup function..thank you very much!!

Though, i have another question, a bit more difficult, i have posted on the forum..


http://www.mrexcel.com/forum/excel-...tween-defined-tables-challeging-new-post.html

could you be kind to look at that if you have the time :D

once again thank you </pre>

 

[jimrward]

have I missed something here, or can the following be used without need to use LOOKUPS of any type

=CHAR(64+(INT(A1/10)+1))

and copied down

 

[Aladin Akyurek]

have I missed something here, or can the following be used without need to use LOOKUPS of any type

=CHAR(64+(INT(A1/10)+1))

and copied down

Yep. I avoided such because of a very possibility of AA for 260 and 269, etc.

 

[Neo4u]

have I missed something here, or can the following be used without need to use LOOKUPS of any type

=CHAR(64+(INT(A1/10)+1))

and copied down

Well actually, for this forum i took a simple example with assigning value A,B,C etc.. but in reality it is a string of text i want to assign, sorry if it is confusing..see post 6