Lookup function

[youngy_6948]

this is going to be a dreadful explanation so i will apologise but i shall try my best basically i have 2 look up tables higher and lower 2 columns where date can be imputed higher and lower but i want my answer to be displayed in only 1 column ill try and demonstrate below
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]higher[/TD]
[TD]lower[/TD]
[TD]grade[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

so i want the grade to be displayed irrespective of whether i put an answer in higher or lower the problem is higher has a different lookup table to lower as they have different grade boundaries can anyone help??? i have two look up tables one called higher and one called lower which has numbers from 0-100 with corresponding grades

thanks in advance

 

[mole999]

can you list your grade boundaries
A+ 100-90
A 89 - 89

etc

 

[youngy_6948]

higher would be;
[TABLE="width: 244"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]0[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]48[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]49[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]51[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]52[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]53[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]54[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]56[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]57[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]58[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]59[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]61[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]62[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]63[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]64[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]66[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]67[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]68[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]69[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]71[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]73[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]74[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]76[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]77[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]78[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]79[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]81[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]82[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]83[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]84[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]85[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]86[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]87[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]88[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]91[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]92[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]93[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD]*A[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD]*A
[/TD]
[/TR]
</tbody>[/TABLE]


lower would be;

[TABLE="width: 112"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]0[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]58[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]59[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]62[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]63[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]64[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]67[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]68[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]69[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]70[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]71[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]72[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]74[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]75[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]76[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]79[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]81[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]82[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]83[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]85[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]86[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]87[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]89[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]91[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]92[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]93[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]94[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]96[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]97[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]98[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

LIKE THIS

Excel Workbook
	A	B	C
1		Higher	Lower
2	*A	100-80
3	A	79-70
4	B	69-60
5	C	59-50	100-70
6	D	49-40	69-60
7	E	39-30	59-50
8	F		49-40
9	G		39-30
10	U	29-0	29-0
Sheet1

 

[mole999]

say your Higher lookup value is in B12 then

=LOOKUP(B12,{0,30,40,50,60,70,80},{"U","E","D","C","B","A","*A"}) returns that

An IF would allow you to check for High existing and if it didn't look in the Low for a range

which could become

=IF(B12<>"",LOOKUP(B12,{0,30,40,50,60,70,80},{"U","E","D","C","B","A","*A"}),LOOKUP(C12,{0,30,40,50,60,70},{"U","G","F","E","D","C"}))

 

[youngy_6948]

legendary thank you i knew there had to be a more dynamic way keep your eyes pinned making a hell of a spreadsheet for the wife so the enslaught will no doubt continue tomorrow

thank again