Forum Discussion

TIGER-18's avatar
TIGER-18
Copper Contributor
Nov 12, 2023
Solved

lookup with different results

Hi, I'm struggling with the below issue in Excel. I have 2 tables and I would like to make a lookup between them but the challenge here is the value array is duplicate and I want to return the invoi...
excel
  • OliverScheurich's avatar
    OliverScheurich
    Nov 12, 2023

    TIGER-18 

    =INDEX($E$2:$E$10,MATCH(1,(A2=$D$2:$D$10)*(COUNTIF($A$2:A2,A2)=$F$2:$F$10),0))

    You can try this formula along with a helper column. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

    =COUNTIF($D$2:D2,D2)

    This is the formula that creates the helper column in the example. The formula is in cell F2 and filled down.

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 12, 2023

TIGER-18 

This formula generates an instance number for duplicate revenue lines in each table.  Then it uses MAP to perform the lookup line at a time rather than as an array.  The IF clause makes XLOOKUP see only those revenue items with a matching instance number.

= LET(
    lookupSeq, COUNTIFS(revenue, revenue, seq, "<="&seq),
    tblSeq,    COUNTIFS(revenueTbl, revenueTbl, RefTbl, "<="&RefTbl),
    MAP(revenue, lookupSeq, LAMBDA(r,s,
      XLOOKUP(r, IF(tblSeq=s, revenueTbl), RefTbl)
    ))
  )

Resources