 # Finding max value in an array that includes #N/A using VLOOKUP

• I've been scouring the net for a function code to allow me to find the max value in an array
that includes #N/A. I need the #N/A to display, to stop the unused values showing in a chart, but
require an add on for the vlookup max function, so i can calculate the max value, as im only getting #N/A

• Re: Finding max value in an array that includes #N/A using VLOOKUP

Hi,

Try this array formula (you need to confirm by pressing CTRL-SHIFT-ENTER, not just ENTER):

Best Regards

• Re: Finding max value in an array that includes #N/A using VLOOKUP

Hi Michael,
thanks for the reply, but i can not get it to function as there is an error in the code.
I add the first array values then im just adding my array values in the second part and it comes up saying there is an error in the code?

• Re: Finding max value in an array that includes #N/A using VLOOKUP

The formula should be something like:
{=MAX(IF(ISNA(A1:A20);0;A1:20))}
can you post back the formula you use?

• Re: Finding max value in an array that includes #N/A using VLOOKUP

=MAX(a1)

VLOOKUP(H60,a1,2)

I can do some of them without the vlookup, just using max if thats a start lol many many thanks for your time x

• Re: Finding max value in an array that includes #N/A using VLOOKUP

You could use this array formula to return the maximum value:

[f]{=MAX(IF(ISNUMBER(A1:A5),A1:A5))][/f]

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Finding max value in an array that includes #N/A using VLOOKUP

forum.ozgrid.com/index.php?attachment/42919/

No guys, its not working either way.. ?>?>
please find the dif examples in attachments
.. Is there a way of hiding the #N/A ?
easily though, say like colour changing the font.

## Files

• Re: Finding max value in an array that includes #N/A using VLOOKUP

Array formula must be entered using the Ctrl + Shift + Enter keys in combination, which you did not do.

Select cell G14 and press the F2 key, then press the above key combination.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Finding max value in an array that includes #N/A using VLOOKUP

Sorry A
totally lost me there, i will pervail.. thank you

• Re: Finding max value in an array that includes #N/A using VLOOKUP

You have Excel 2010, right?

Try using AGGREGATE function, i.e.

=AGGREGATE(4,6,D7:D29)

[4 indicates MAX, 6 indicates "ignore errors"]

• Re: Finding max value in an array that includes #N/A using VLOOKUP

Quote

totally lost me there, i will pervail.. thank you

When you type the formula into the cell, rather than press the Enter key to exit the cell, you press the Ctrl + Shift + Enter keys in combination.
Excel then knows this is an array formula and applies the curly brackets.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Finding max value in an array that includes #N/A using VLOOKUP