xlookup multiple criteria?

  • I have a table and I want to get the value of a cell based on multiple criteria. Here is my currently formula


    =([@[Spec Mean]]-((XLOOKUP("BSL",[v.Con],[Spec Mean]))))/ (XLOOKUP("BSL",[v.Con],[Spec Mean]))


    The XLOOKUP function is returning the value for the ROW with "BSL" in the [v.Con] column but I have instances where there are multiple rows in that column with "BSL" and I need a second column to further specific the correct row. How do I include a second column requirement?


    Thanks

  • 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