1

I have two tables that I am trying to combine. Table one (Left in image) has a date of an inventory transfer and a combined customerID and Part#. Table 2 (right in image) has an invoice date, unit price, and combined CustomerID and Part#. I want to do a lookup in the first table to give me the last invoiced price (Invoice date < transfer date) prior to the transfer date for each CustomerID and Part# combination. I am at a loss on how to tackle this without chopping up all of my data which is a few hundred thousand rows. Any help is appreciated. Thank you in advance.

enter image description here

I have tried variations of index matches with nested if statements and haven't got anything to work.

teylyn
  • 34,374
  • 4
  • 53
  • 73
Ryrytheguyguy
  • 11
  • 1

3 Answers3

2

Microsoft-365 solution.

=TAKE(SORT(FILTER($E$2:$F$5,($E$2:$E$5<A2)*($G$2:$G$5=B2),"NA"),1,-1),1,-1)

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
1

Exact Match or Next Smaller (-1)

enter image description here

=LET(slData,F2:H5,sdCol,1,siCol,3,spCol,2,
        dlData,A2:B15,ddCol,1,diCol,2,
        dNA,"No Date",iNA,"No ID",
    sl,SORT(slData,sdCol),
    si,INDEX(sl,,siCol),
    dd,INDEX(dlData,,ddCol),
    di,INDEX(dlData,,diCol),
MAP(dd,di,LAMBDA(rd,ri,LET(
    sfi,si=ri,
    IF(SUM(N(sfi))=0,iNA,LET(
        sf,FILTER(sl,sfi),
        sm,XMATCH(rd,INDEX(sf,,sdCol),-1,2),
        IF(ISNA(sm),dNA,INDEX(sf,sm,spCol))))))))
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
0

This is a 2 step process and will require you to properly sort the table you are doing going to be looking up information. Because the Match function can only lookup 1 item, we will use the Offset function to dynamically define the range.

Step 1 Sort your data in Table 2 by CustomerID and Part# Ascending, then by Invoice Date ascending.

Step 2 Build the formula. I'm going to be indenting and using multiple lines for readability, then provide the full formula to copy and paste. I'm basing the formula on the picture. I assume the formula will be in cell C2.

INDEX(
    OFFSET(
        $F$2:$F$5, 
        MATCH(B2,$G$2:$G$5,0)-1, 
        0, 
        COUNTIFS($G$2:$G$5,B2)
    ),
    MATCH(
        A2, 
        OFFSET(
            $E$2:$E$5, 
            MATCH(B2,$G$2:$G$5,0)-1
            ,0
            ,COUNTIFS($G$2:$G$5,B2)
        ),
        1
    )
)

Collapsed Excel Formula =INDEX(OFFSET($F$2:$F$5,MATCH(B2,$G$2:$G$5,0)-1,0,COUNTIFS($G$2:$G$5,B2)),MATCH(A2,OFFSET($E$2:$E$5,MATCH(B2,$G$2:$G$5,0)-1,0,COUNTIFS($G$2:$G$5,B2)),1))

Explanation

Offset allows you to dynamically select a range. In this case, because the Match function can only lookup 1 value at a time, we can use Offset to overcome this limitation.

In the Index function, we dynamically select a subset of ranges within column F by matching the CustomerID & PartID with the first occurrence of that combo in column G. The offset fuction's 4th argument allows you to select the "Height" of your range. Thus, we use CountIFs to control how big our set is. In the Match function, we do the same exact logic, but instead of looking to return column F, we are looking to return Column E. By setting this Match function to be 1, it will return the largest value it finds that is less than or equal to the lookup value.

Again, this method REQUIRES that you have your data sorted properly. If your data isn't sorted properly, you may get bad results or even falsely flagged as not found. Here is a screenshot from my excel showing the formula in Cell C2 which currently returning 1.5 enter image description here

MartyMcfly0033
  • 168
  • 11