Thread: Lookup

Hi and welcome to the board

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

1st:
Form help on lookup:

The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

So your existing formula isn't going to work reliably

2nd
Assuming we are going to replace your formula with index/match, also from help:

•If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

This means we don't have to list all the scores that would lead to a certain grade, just the ones at the boundary.

Based on these two things:
1. Use this replacement formula:
=INDEX(A56:A91,MATCH(E46,B56:B91,-1)) and adjust as appropriate

2. Revise your data, put the largest of all the comma-separated numbers into each cell
63, 64 --> 64
46, 47, 48 --> 48

3. Don't leave any gaps
Math should read:
60
60
59

or

60
59
59

but not

60
0
59

Top tips:
If you set your font size to ten instead of eight you can zoom out a bit so you don't have those enormous row numbers and column letters.
I highly recommend those help files

HTH