两列查找，其中单元格值与前提范围一​​致 [英] Two Column Lookup with a cell value falling in with a prerequisite range

问题描述

我有一个代码列表，如下所示：

  RF206 
 RT205 
 RG20 
 etc 
  

我有一个如下所示的代码类别列表：

 前缀范围开始范围结束类别
 RF 1 52投资成本
 RF 53 210建筑成本
 RT 1 200工资
 RT 201 256奖金
 RG 1 19餐厅
 RG 20 30运输
  

我想做的是能够将代码与类别相匹配：

  RF206 Building费用
 RT205奖金
 RG20运输
  

我明白VLOOKUP不能工作，因为每个前缀（RT，RF等）有多个值，所以我不能只是查找代码前缀。我需要一种方法来组合寻找前缀的完全匹配，并检查数字部分是否在范围的两端之间，以返回相应的类别。

解决方案

这只是一个两列查询（有很多

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.

解决方案

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).

这篇关于两列查找，其中单元格值与前提范围一​​致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！