SOLVED

# Reference/Lookup Formula Help

Occasional Contributor

# 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.

2 Replies
best response confirmed by T-Meyers (Occasional Contributor)
Solution

# Re: Reference/Lookup Formula Help

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)),"")

# Re: Reference/Lookup Formula Help

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