Modified vlookup returning multiple values

  • Background: The pink and blue shaded cells are commodity codes that have leading zeroes or are alpha-numeric codes. Our system rejects these types of codes and we require either a replacement code from the CONSOLIDATED TAXONOMY FILE or if no other alternative code is available, we have to generate a new commodity code (I will do this part). The lookup value is the NEW COMMODITY NAME (Column H), the results from the formula can be placed horizontally beginning at (Column U) ALTERNATE VISION COMMODITY CODE in the “ASM TAXONOMY FILE 040616”.


    The range where the possible return matches is can be found in the tab “CONSOLIDATED TAXONOMY FILE”, using COMMODITY (Column G) and returning the value COMMODITY CODE (Column H). There is a possibility that there is only 1 commodity code choice which may be the leading zero or alpha-numeric code we are already using – I need to validate this.

  • Re: Modified vlookup returning multiple values


    Try, in U2:


    [COLOR="#0000FF"]=IFERROR(INDEX('CONSOLIDATED TAXONOMY FILE'!$H$2:$H$3722,SMALL(IF('CONSOLIDATED TAXONOMY FILE'!$G$2:$G$3722=H2,ROW('CONSOLIDATED TAXONOMY FILE'!$H$2:$H$3722)-ROW('CONSOLIDATED TAXONOMY FILE'!$H$2)+1),COLUMNS($U2:U2))),"")[/COLOR]


    confirmed with CTRL+SHIFT+ENTER, not just Enter, copied across as many columns as you feel you might need for alternate matches, then copied down the table.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016