Nov 24 2020 07:03 AM
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.
Nov 24 2020 07:22 AM
SolutionThe 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)),"")
Nov 24 2020 07:54 AM
@Hans VogelaarThank you for this quick answer. It is much appreciated!
Nov 24 2020 07:22 AM
SolutionThe 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)),"")