Forum Discussion

cjscorcoran's avatar
cjscorcoran
Copper Contributor
Sep 23, 2021

Idex Match

Hi Guys,

 

Im trying to do an index match on the below data to try and look up from the description in column J to match what's in columns B/C and to return the item in column A.

The formulas seems to work and pulls in the correct info. however if column B is blank the data just pulls in the wrong data and duplicates it.

 

Could anyone advise or maybe tell me a better way to look up the data in column J to B/C?

 

Thanks

 

excel
Formulas and Functions

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 23, 2021

    cjscorcoran 

    Adjust the ranges as needed, but don't use entire columns in the following formula.

    =IFERROR(INDEX($A$2:$A$100,MATCH(1,($B$2:$B$100<>"")*ISNUMBER(SEARCH($B$2:$B$100,J2)),0)),"")

     

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Sep 23, 2021
    Please post an Excel file rather than screen-shots, we don't like re-entering your data. Make sure the workbook does not contain any sensitive information.

Resources