多条件查询

helloexcel2014

求b4：b16的公式，比较麻烦的是b2为一个数值，要匹配基数表中多少名到多少名，也就是说如b2=9，就要匹配1-10名内对应的数值

szl3178

1. =MAP(A4:A16,LAMBDA(x,OFFSET(基数!B9,MATCH(x,基数!A10:A28,),MATCH(1,FREQUENCY(B2,--REGEXP(基数!B9:J9,"-\K\d+")),)-1)))

复制代码

姚阿福

B4=INDEX(基数!$A$8:$L$28,MATCH($A4,基数!$A$8:$A$28,0),IF($B$2<101,ROUNDDOWN($B$2/10,0)+2,11))

下拉

edwin11891

1. =XLOOKUP(A4,基数!A$10:A$22,OFFSET(基数!A$9,1,ROUNDUP($B$2/10,0),13),"",)

复制代码

gwjkkkkk

1. =VLOOKUP(A4,HSTACK(基数!$A$10:$A$28,CHOOSECOLS(基数!$B$10:$L$28,LOOKUP($B$2,--TEXTBEFORE(TEXTAFTER(基数!$A$9:$K$9,"-"),"名"),SEQUENCE(,COLUMNS(基数!$A$9:$K$28))))),2,FALSE)

复制代码

daiyanwei

=VLOOKUP(A4,基数!$A$10:$K$22,ROUNDUP($B$2/10,0)+1,0)

zhangcheng6688

借楼学习一下数组，打扰了
Sub test()
arr = Sheets("2.2").Range("a4:b16")
brr = Sheets("基数").[a8].CurrentRegion
syrs = Sheets("2.2").[b2]
For i = 2 To UBound(brr, 2) - 1
   qzfw = Split(Mid(brr(2, i), 1, Len(brr(2, i)) - 1), "-")
   If syrs >= Val(qzfw(0)) And syrs <= Val(qzfw(1)) Then
      For j = 1 To UBound(arr)
         For k = 3 To UBound(brr)
            If arr(j, 1) = brr(k, 1) Then
               arr(j, 2) = brr(k, i)
            End If
         Next
      Next
   End If
Next
Sheets("2.2").[a4].Resize(UBound(arr), UBound(arr, 2)) = arr
End Sub

沈默00

=INDEX(基数!$B$10:$K$28,MATCH($A4,基数!$A$10:$A$28,),XMATCH($B$2,--LEFT(基数!$B$9:$K$9,FIND("-",基数!$B$9:$K$9)-1),-1))