Idex Match

%3CLINGO-SUB%20id%3D%22lingo-sub-2776959%22%20slang%3D%22en-US%22%3EIdex%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776959%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIm%20trying%20to%20do%20an%20index%20match%20on%20the%20below%20data%20to%20try%20and%20look%20up%20from%20the%20description%20in%20column%20J%20to%20match%20what's%20in%20columns%20B%2FC%20and%20to%20return%20the%20item%20in%20column%20A.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22cjscorcoran_0-1632369614808.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312312i82F065E5AE79B6B1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22cjscorcoran_0-1632369614808.png%22%20alt%3D%22cjscorcoran_0-1632369614808.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20formulas%20seems%20to%20work%20and%20pulls%20in%20the%20correct%20info.%20however%20if%20column%20B%20is%20blank%20the%20data%20just%20pulls%20in%20the%20wrong%20data%20and%20duplicates%20it.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22cjscorcoran_1-1632369770995.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312313i025B5C3E4A8B9043%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22cjscorcoran_1-1632369770995.png%22%20alt%3D%22cjscorcoran_1-1632369770995.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20anyone%20advise%20or%20maybe%20tell%20me%20a%20better%20way%20to%20look%20up%20the%20data%20in%20column%20J%20to%20B%2FC%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2776959%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2777493%22%20slang%3D%22en-US%22%3ERe%3A%20Idex%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2777493%22%20slang%3D%22en-US%22%3EPlease%20post%20an%20Excel%20file%20rather%20than%20screen-shots%2C%20we%20don't%20like%20re-entering%20your%20data.%20Make%20sure%20the%20workbook%20does%20not%20contain%20any%20sensitive%20information.%3C%2FLINGO-BODY%3E
Occasional Visitor

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.

cjscorcoran_0-1632369614808.png

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.

cjscorcoran_1-1632369770995.png

 

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

 

Thanks

 

2 Replies
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.

@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)),"")