Forum Discussion

MrStern's avatar
MrStern
Copper Contributor
Mar 03, 2020

Lookup across multiple tables

Hi,

 

I have a sales table with a unique sales entry number for each sale.

I also have several placement tables.  These tables indicate the unique sales entry number for the placement.

What I need to do, is on the sales table I need to look up the entry number from the various placement tables.

 

I am capable with index match or vlookups against one table, but have never worked on trying to do this against multiple tables and have had no luck with it so far.

 

Any help would be appreciated.

excel
Formulas and Functions

2 Replies

  • Savia's avatar
    Savia
    Iron Contributor
    Mar 03, 2020
    I don't think there's a much simpler way than just making three INDEX/MATCH and using IFERROR to determine which one works:

    =IFERROR(IFERROR(INDEX(Tbl_Accrual[Entry No],MATCH(Tbl_Sales[@[Placement number]],Tbl_Accrual[Placement],0)),INDEX(Tbl_Current[Entry No],MATCH(Tbl_Sales[@[Placement number]],Tbl_Current[Placement],0))),INDEX(Tbl_Cutoff[Entry No],MATCH(Tbl_Sales[@[Placement number]],Tbl_Cutoff[Placement],0)))
    • MrStern's avatar
      MrStern
      Copper Contributor
      Mar 03, 2020

      SaviaThat worked wonderfully,

      Simplicity is often the best. 

      I had been trying all sorts of crazy things I had found online involving 'indirect' and 'summary' and others, it absolutely melted my head.

       

      Thanks!

Resources