Forum Discussion
Reference/Lookup Formula Help
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.
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)),"")
2 Replies
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)),"")
HansVogelaarThank you for this quick answer. It is much appreciated!
