The XLOOKUP function search for a value in a specific column within a range, and then lookup the corresponding value in another column. You do not need to know the position (order) of the column to search and to lookup. You just need to specify the two column headings.
This function is especially useful when you don’t know the order of the columns or the columns are going to be re-arranged.
XLOOKUP(heading, lookup_value, target_heading, data_range)
heading - Required. is column heading of the column in which XLOOKUP will try to locate an exact match of the lookup_value.
lookup_value - Required. A value that XLOOKUP searches for in the column with the heading specified. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.
target_heading - Required. is column heading of the column from which XLOOKUP will return the value in the same row as the lookup_value.
data_range - Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name.
Find the Department of staff with Last name “Burke”:
=XLOOKUP("Last name","Patten","Department",A1:F7) returns Fixed Income
Find the Title of staff with Last name “Burke”:
=XLOOKUP("Last name","Patten","Title",A1:F7) returns Sales Rep.
Find the First name of the first staff in the list in Fixed Income Department:
=XLOOKUP("Department”,“Fixed Income","First name",A1:F7) returns Olivier