Forum Discussion
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 invoice number with different results for each "Revenue".
=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.
3 Replies
Hi, if you have office365, you can create a helper column using COUNTIF as OliverScheurich suggested, and create another helper column using TRANSPOSE + FILTER to display all the invoice numbers associated with a given revenue. and then use "INDEX" function to choose one of the invoice numbers.
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) )) )=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.
