All data is in anExcel Tablein the range B5:D15 named “data”.
This problem can be solved with theXLOOKUP functionor withINDEX and MATCHtogether withBoolean logic.
Both options are explained below.
XLOOKUP function
The XLOOKUP function is a modern replacement for theVLOOKUP function.
A key benefit of XLOOKUP is that it can handlearray operationsas thelookup_arrayorreturn_array.
This means we can construct thelookup_arraywe need as part of the formula.
It is simply a common convention when usingBoolean logicin lookup formulas.
Notice the third value is TRUE, while all others are FALSE.
XLOOKUP matches 1 and returns the third row ofdata.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
ABS converts negative numbers to positive numbers, and positive numbers are unaffected.
it’s possible for you to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
Often, MATCH is combined with the…
