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
    as the max value answer.

    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):
    =MAX(IF(ISNA(your array range);0;you array range))


    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


    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.

    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.

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


    Hi Daddy
    sure i use 2010 and your aggregate function did the trick many thanks, it looks great now.
    I take it they will be fine on any amount of variable values?
    thanks to all for your help x

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login