Search code examples
arraysexcelvbarangematch

Two-Way Lookup w/ Index and Match

Trying to match row A1:L1 and columns B2:B16,C2:C16,F2:F16,G2:G16,J2:J16,K2:K16 and return their Qtys. The inputs/outputs are in the red/green boxes. The second match in the formula is working when I step through it. The first match in the formula produces a #N/A and is the issue.

10/21/22 Apple Color Code Apple Color Code Berry 10/21/22 Berry 11/4/22 Apple Color Code Apple Color Code Berry 11/4/22 Berry 12/21/22 Apple Color Code Apple Color Code Berry 12/21/22 Berry
1 221021apple1 221021berry1 45 5 221104apple10 221104berry7 4,545 65 221221apple2 221221berry4 22
2 221021apple2 221021berry20 5 9 221104apple32 221104berry8 1,313 11 221221apple12 221221berry13 2
3 221021apple3 221021berry39 8 4 221104apple54 221104berry9 1,212 5,656 221221apple22 221221berry22 2
5 221021apple4 221021berry58 56 45 221104apple76 221104berry10 22 42 221221apple32 221221berry31 0
8 221021apple5 221021berry77 3 65 221104apple98 221104berry11 22 54,242 221221apple42 221221berry40 333
6,666 221021apple6 221021berry96 88 98 221104apple120 221104berry12 222 4 221221apple52 221221berry49 222
2 221021apple7 221021berry115 66 78 221104apple142 221104berry13 2 424 221221apple62 221221berry58 244
3 221021apple8 221021berry134 44 32 221104apple164 221104berry14 22 2,323 221221apple72 221221berry67 44
4,744 221021apple9 221021berry153 22 56 221104apple186 221104berry15 2 424 221221apple82 221221berry76 123
55,151 221021apple10 221021berry172 88 12 221104apple208 221104berry16 2 5 221221apple92 221221berry85 111
2,121 221021apple11 221021berry191 12 5,555 221104apple230 221104berry17 2 4,242 221221apple102 221221berry94 456
221021apple12 221021berry210 36 2,222 221104apple252 221104berry18 5 1,212 221221apple112 221221berry103 789
48,484 221021apple13 221021berry229 58 888 221104apple274 221104berry19 2 444 221221apple122 221221berry112 888
6,541 221021apple14 221021berry248 5 7 221104apple296 221104berry20 4 2,222 221221apple132 221221berry121 55
77 221021apple15 221021berry267 96 43 221104apple318 221104berry21 4 42 221221apple142 221221berry130 222
A1:L1 Col. B,C,F,G,J,K Qty
10/21/22 Apple 221021apple10 55,151 Returns A11
12/21/22 Berry 221221berry49 222 Returns L7

Formula in cell C19:

=INDEX(Sheet1!A2:L16, MATCH(Sheet1!B19,Sheet1!A2:L16,0), MATCH(Sheet1!A19,Sheet1!A1:L1,0))

Formula in cell C20:

=INDEX(Sheet1!A2:L16, MATCH(Sheet1!B20,Sheet1!A2:L16,0), MATCH(Sheet1!A20,Sheet1!A1:L1,0))

enter image description here

Solution

  • C19 formula

    =OFFSET($A$1,MATCH(B19,OFFSET($A$2:$A$16,,IF(MOD(MATCH(Sheet1!A19,Sheet1!$A$1:$L$1,0),2)=0,MATCH(Sheet1!A19,Sheet1!$A$1:$L$1,0)-1,MATCH(Sheet1!A19,Sheet1!$A$1:$L$1,0)+1)-1),0),MATCH(Sheet1!A19,Sheet1!$A$1:$L$1,0)-1)

    C20 formula

    =OFFSET($A$1,MATCH(B20,OFFSET($A$2:$A$16,,IF(MOD(MATCH(Sheet1!A20,Sheet1!$A$1:$L$1,0),2)=0,MATCH(Sheet1!A20,Sheet1!$A$1:$L$1,0)-1,MATCH(Sheet1!A20,Sheet1!$A$1:$L$1,0)+1)-1),0),MATCH(Sheet1!A20,Sheet1!$A$1:$L$1,0)-1)

    If you are using M365, let formula is simpiler.

    C19 formula

    =LET(c,MATCH(Sheet1!A19,Sheet1!$A$1:$L$1,0),
    col,OFFSET($A$2:$A$16,,IF(MOD(c,2)=0,c-2,c)),
    r,MATCH(Sheet1!B19,col,0),
    OFFSET($A$1,r,c-1))