#N/A value wont go away! Any solutions out there?

[PaulGotts]

Hi There,

I have an array formula which spans 250 columns and transposes a varying number of values using OFFSET as my output.

When I get less than 250 values there appears an "#N/A" in the cell which can not be removed by the IFERROR method or IF(ISNA method.

=TRANSPOSE(OFFSET(INDEX(RetailDetail!$C$3:$IR$3,,MATCH(LEFT(Graph!$D$2,4),LEFT(RetailDetail!$C$3:$IR$3,4),0)),,,,SUM(--(LEFT(RetailDetail!$C$3:$IR$3,4)=LEFT(Graph!$D$2,4)))))

Any idea how I can get rid of these pesky NA values? Data output is below.

Thanks,
Paul.

Mid Atlantic

Northeast

Southeast

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A | | | V

<TBODY>
</TBODY>

 

[Akashwani]

Hi Paul,

I don't fully understand what your formula is doing and I'm not sure if this will work for you or not, but perhaps it will give you some idea how to deal with the problem...

Sample Data...

Excel Workbook
	B	C	D	E	F	G	H	I	J
2
3	Data	Mid Atlantic	Northeast	Southeast	Southwest	Northwest	South SouthWest	North NorthWest
4
RetailDetail

Result...

Excel Workbook
	B	C	D	E
1			Lookup
2		Result	South
3		Southeast
4		Southwest
5		South SouthWest
6
Graph

This is an array formula.

I hope that gives you some ideas.
If nothing else, it has bumped you back up and hopefully someone else will give you the solution.

Good luck.

Ak