SOLVED

Reference/Lookup Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1927301%22%20slang%3D%22en-US%22%3EReference%2FLookup%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1927301%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20hoping%20to%20get%20some%20assistance%20with%20a%20single%20formula%20when%20reference%20and%20looking%20up%20information%20from%20matrix%20view.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20notes%20in%20Cell%20%22I5%22.%20Any%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1927301%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-1927390%22%20slang%3D%22en-US%22%3ERe%3A%20Reference%2FLookup%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1927390%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F572655%22%20target%3D%22_blank%22%3E%40T-Meyers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20B5%20on%20the%20Active%20Sheet%20returns%205000%20as%20a%20text%20value.%3C%2FP%3E%0A%3CP%3ECurrently%2C%205000%20in%20cell%20A22%20on%20the%20Case%20Packs%20sheet%20is%20a%20number%2C%20so%20it%20doesn't%20match%20B5.%3C%2FP%3E%0A%3CP%3EYou%20should%20change%20all%20the%20numbers%20in%20column%20A%20on%20the%20Case%20Packs%20sheet%20to%20text%20by%20prefixing%20them%20with%20'.%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX('Case%20Packs'!%24G%244%3A%24AF%24104%2CMATCH(B5%2C'Case%20Packs'!%24A%244%3A%24A%24104%2C0)%2CMATCH(F5%2C'Case%20Packs'!%24G%242%3A%24AF%242%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%20if%20you%20want%20to%20suppress%20the%20%23N%2FA%20error%20if%20there%20is%20no%20match%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX('Case%20Packs'!%24G%244%3A%24AF%24104%2CMATCH(B5%2C'Case%20Packs'!%24A%244%3A%24A%24104%2C0)%2CMATCH(F5%2C'Case%20Packs'!%24G%242%3A%24AF%242%2C0))%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi Everyone,

 

I am hoping to get some assistance with a single formula when reference and looking up information from matrix view.

 

Please see notes in Cell "I5". Any help is greatly appreciated.

2 Replies
Best Response confirmed by T-Meyers (Occasional Contributor)
Solution

@T-Meyers 

The formula in B5 on the Active Sheet returns 5000 as a text value.

Currently, 5000 in cell A22 on the Case Packs sheet is a number, so it doesn't match B5.

You should change all the numbers in column A on the Case Packs sheet to text by prefixing them with '.

You can then use

 

=INDEX('Case Packs'!$G$4:$AF$104,MATCH(B5,'Case Packs'!$A$4:$A$104,0),MATCH(F5,'Case Packs'!$G$2:$AF$2,0))

 

or if you want to suppress the #N/A error if there is no match:

 

=IFERROR(INDEX('Case Packs'!$G$4:$AF$104,MATCH(B5,'Case Packs'!$A$4:$A$104,0),MATCH(F5,'Case Packs'!$G$2:$AF$2,0)),"")

@Hans VogelaarThank you for this quick answer. It is much appreciated!