#NUM! error

I'm getting #NUM! error. How do I need to format my data to fix this? I attached a spreadsheet so that you can see.

The formulas worked fine when I manually typed in the data but the data I'm using came from someone else that I'm trying to use these formulas in.

That is because the formula that is currently in there is expecting Q and P (in U1 and V1), not TIER Q and TIER P.

You can either:

A) Change TIER Q and TIER P to Q and P

or

B) Change the formula in U2 from U$1 to RIGHT(U$1)

That is:

=INDEX(Sheet2!$B$2:$K$15266,MATCH($A2,Sheet2!$A$2:$A$15266,0),SMALL(IF(LEFT(Sheet2!$B$1:$K$1)=RIGHT(U$1),COLUMN(Sheet2!$B$1:$K$1)-1),COUNTIF($A$2:$A2,$A2))) Ctrl Shift Enter

Originally Posted by 63falcondude

That is because the formula that is currently in there is expecting Q and P (in U1 and V1), not TIER Q and TIER P.

The only thing I changed was =T$1 to be =U$1 since I inserted a new column A. I needed the original column A to not change. The uploaded need it to have blanks.

When I made those changes it worked fine in my test file but not on the real one for some reason.

Oh crap I see what you mean with what I did. Sorry!!!

it's working now