SOLVED

Reference/Lookup Formula Help

Copper 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 (Copper 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! 

1 best response

Accepted Solutions
best response confirmed by T-Meyers (Copper 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)),"")

View solution in original post