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
Actually I just realized I can just use SUMIF
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
Don’t have an account yet? Register yourself now and be a part of our community!
