Posts by gyankosh.net

    xlookup is a good function which can be used very easily


    XLOOKUP FUNCTION is a new function introduced in some of the EXCEL OPTIONS, such as EXCEL 365, EXCEL FOR ANDROID ETC.


    The syntax ( the way how formula is phrased for excel) of XLOOKUP is

    =XLOOKUP(VALUE TO BE FOUND , LOOKUP TABLE , RETURN ARRAY , ERROR MESSAGE , MATCH MODE , SEARCH MODE )

    VALUE TO BE FOUND The value to be found and matched

    LOOKUP TABLE The range of cells, where we will try to find the match


    RETURN ARRAY The returning array or a set of values to be returned after the match has been made


    ERROR MESSAGE If value is not found, this message will be displayed. Its kind of error handling.


    MATCH MODE

    0 – Exact match. If none found, return #N/A. This is the default.

    -1 – Exact match. If none found, return the next smaller item.

    1 – Exact match. If none found, return the next larger item.

    2 – A wildcard match where *, ?, and ~ have special meaning.

    SEARCH MODE

    1 – Perform a search starting at the first item. This is the default.

    -1 – Perform a reverse search starting at the last item.

    2 – Perform a binary search that relies on LOOKUP VALUE being sorted in ascending order. If not sorted, invalid results will be returned.

    -2 – Perform a binary search that relies on LOOKUP VALUE being sorted in descending order. If not sorted, invalid results will be returned.

    examples are present in this link