Two Column Lookup with a cell value falling in wit

问题:

I have a list of codes, that looks like this:

RF206
RT205
RG20
etc

And I have a list of code categories that looks like this:

Prefix Range start Range end Category
RF         1          52     Investment costs
RF        53          210     Building costs
RT        1           200     Salaries
RT       201          256     Bonuses
RG        1            19     Restaurant
RG       20            30     Transport

What I would like to do is be able to match codes to categories, like this:

RF206 Building costs
RT205 Bonuses
RG20 Transport

I appreciate that a VLOOKUP can't work, as there are multiple values for every prefix (RT, RF, etc), so I can't just lookup the codes prefix. I need a way to combine looking for an exact match for the prefix and to check if the numerical part falls between the two ends of the range, to bring back the corresponding category.

回答1:

This is just a two-column-lookup (there are many examples on this site) with some LEFT and MID string function parsing thrown in.

In B2 as a standard formula,

=INDEX(Z:Z, AGGREGATE(14, 6, ROW(W$1:INDEX(W:W, MATCH("zzz",W:W )))/((W$1:INDEX(W:W, MATCH("zzz",W:W ))=LEFT(A2, 2))*(X$1:INDEX(X:X, MATCH("zzz",W:W ))<=VALUE(MID(A2, 3, 9)))), 1))

Fill down as necessary. The ranges look a little more complicated than normal because they are dynamic and do not have to be adjusted if additional entries are made (or removed).