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

higher	lower	grade

<tbody>
</tbody>

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;

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

<colgroup><col><col></colgroup><tbody>
</tbody>

lower would be;

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

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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