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:
[TABLE="width: 288"]
<colgroup><col style="width: 48pt;" span="6" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Level-1[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Level-2[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Score[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]A[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent"]A[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl65, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl65, bgcolor: transparent"]B[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]29[/TD]
[TD="class: xl65, bgcolor: transparent"]C[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, bgcolor: transparent"]C[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]39[/TD]
[TD="class: xl65, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, bgcolor: transparent"]B[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]49[/TD]
[TD="class: xl65, bgcolor: transparent"]E[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]70[/TD]
[TD="class: xl65, bgcolor: transparent"]H[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]59[/TD]
[TD="class: xl65, bgcolor: transparent"]F[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]75[/TD]
[TD="class: xl65, bgcolor: transparent"]H[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]69[/TD]
[TD="class: xl65, bgcolor: transparent"]G[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent"]B[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]70[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]79[/TD]
[TD="class: xl65, bgcolor: transparent"]H[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]80[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]89[/TD]
[TD="class: xl65, bgcolor: transparent"]I[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]90[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]99[/TD]
[TD="class: xl65, bgcolor: transparent"]J[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]109[/TD]
[TD="class: xl65, bgcolor: transparent"]K[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]110[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]119[/TD]
[TD="class: xl65, bgcolor: transparent"]L[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]120[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]129[/TD]
[TD="class: xl65, bgcolor: transparent"]M[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]130[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]139[/TD]
[TD="class: xl65, bgcolor: transparent"]N[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]140[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]149[/TD]
[TD="class: xl65, bgcolor: transparent"]O[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]150[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]159[/TD]
[TD="class: xl65, bgcolor: transparent"]P[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]160[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]169[/TD]
[TD="class: xl65, bgcolor: transparent"]Q[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]170[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]179[/TD]
[TD="class: xl65, bgcolor: transparent"]R[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]180[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]189[/TD]
[TD="class: xl65, bgcolor: transparent"]S[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]190[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]199[/TD]
[TD="class: xl65, bgcolor: transparent"]T[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]200[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]209[/TD]
[TD="class: xl65, bgcolor: transparent"]U[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]210[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]219[/TD]
[TD="class: xl65, bgcolor: transparent"]V[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]220[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]229[/TD]
[TD="class: xl65, bgcolor: transparent"]W[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]230[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]239[/TD]
[TD="class: xl65, bgcolor: transparent"]X[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]240[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]249[/TD]
[TD="class: xl65, bgcolor: transparent"]Y[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]250[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]259[/TD]
[TD="class: xl65, bgcolor: transparent"]Z[/TD]
[/TR]
</tbody>[/TABLE]

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