Forum Discussion

MeMacz1138's avatar
MeMacz1138
Copper Contributor
Mar 25, 2019
Solved

Index Match problem

I am having a problem with what should be a simple solution.  In the example below, I am trying to create an Index Match formula to look for one value in a table and pull in the Catalog ID.  For example, if I look for PBC-1430, I need 2D4 to populate my cell:

Catalog IDCust A Item #sCust B Item #sCust C Item #sCust D Item #sCust E Item #sCust F Item #s
A105BLL05BAA10000A1000AFABAT2000 
0B104BLL04BAA2000B2000AFABATC2000 
C506B1L06B1A   PBC-1430
2D408D1LL08D1AA   PBC-1460

 

I originally tried using a vlookup, but that didn't work.  Any assistant would be much appreciated.

  • Twifoo's avatar
    Twifoo
    Mar 26, 2019
    To remedy the possible occurrence of duplicates, I suggest this formula in Inventory!A16:
    =IFERROR(INDEX('Original Catalog & Customers'!B$6:B$16,
    SUMPRODUCT(MAX(ROW('Original Catalog & Customers'!C$6:I$16)*
    ('Original Catalog & Customers'!C$6:I$16=B16)))-5),
    "No such")

10 Replies

  • Hi MeMacz1138 ,

     

    That is like

    =IFERROR(INDEX($A$2:$A$1000,MATCH(J4,$G$2:$G$1000,0)),"no such")

    if the value you are looking for is in J4

    • MeMacz1138's avatar
      MeMacz1138
      Copper Contributor

      SergeiBaklan Thank you for responding so quickly!  Is there a way to do the lookup in all cells from B1 through G5?  Rather than only looking in Column G?  

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        MeMacz1138 , it depends on what you'd like to do. If multiple criteria, like return value from A if in G we have some1 AND in C some2, etc. - that's one situation. If pull value not from column A but another one that will be third parameter INDEX. Similar to VLOOKUP when you have columns number as parameter.

         

        What is your case exactly? Perhaps just small sample to explain.

Resources