Cross lookup using Index and Match

%3CLINGO-SUB%20id%3D%22lingo-sub-1784631%22%20slang%3D%22en-US%22%3ECross%20lookup%20using%20Index%20and%20Match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1784631%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bcroft_0-1602775881581.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226948iDDE5774095DA4789%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bcroft_0-1602775881581.png%22%20alt%3D%22bcroft_0-1602775881581.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20blue%20table%20is%20on%20one%20sheet%20the%20input%20values%20is%20on%20another%2C%20The%20table%20gets%20pulled%20as%20a%20report%20and%20i%20manually%20enter%20the%20input%20data.%20What%20im%20looking%20for%20is%20to%20auto%20populate%20the%20assets%20when%20i%20input%20the%20account%20number.%20Note%20the%20report%20does%20not%20input%20the%20%22-0001%22%20so%20to%20resolve%20this%20ive%20been%20using%20-left%2C%20%3Dright%20to%20separate%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Enow%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22473px%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22150px%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23008080%22%3Erow%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22146px%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23008080%22%3Ecolumn%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22177px%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3Eindex%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22150px%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23008080%22%3E%3DMATCH(E9%2CA%3AA%2C0)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22146px%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23008080%22%3E%3DMATCH(F9%2C4%3A4%2C0)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22177px%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3DINDEX(A4%3AN5%2CG12%2CI12)%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22150px%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23008080%22%3E%3DMATCH(E10%2CA%3AA%2C0)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22146px%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23008080%22%3E%3DMATCH(F10%2C4%3A4%2C0)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22177px%22%20height%3D%2230px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3DINDEX(A4%3AN5%2CG13%2CI13)%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMatch%20Formula%20works%20but%20the%20Index%20returns%20in%20error%2C%20What%20am%20i%20missing%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1784631%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

bcroft_0-1602775881581.png

 

the blue table is on one sheet the input values is on another, The table gets pulled as a report and i manually enter the input data. What im looking for is to auto populate the assets when i input the account number. Note the report does not input the "-0001" so to resolve this ive been using -left, =right to separate them.

 

now:

rowcolumnindex
=MATCH(E9,A:A,0)=MATCH(F9,4:4,0)=INDEX(A4:N5,G12,I12)
=MATCH(E10,A:A,0)=MATCH(F10,4:4,0)=INDEX(A4:N5,G13,I13)

 

Match Formula works but the Index returns in error, What am i missing?

1 Reply

@bcroft 

That could be

image.png