Using Xlookup with match function to link on matching headings?

Copper Contributor

Is there a way to replicate - in Xlookup -  combining the match function with a vlookup so that it returns a result based on a column heading. I'm guessing not because match uses a cell to match on and xlookup returns an array result. For example in the attached file how could I use an Xlookup (set up in the Xlookup sheet for the Product name) to copy across the correct entries for Unit Price, Units in Stock and Units on Order - without me having to do each one manually. Thanks. 

2 Replies

@Anne Walsh 

Not exactly what you asked, but the formula in C4

 

=XLOOKUP(Results[@[Product ID]:[Product ID]],Products[[Product ID]:[Product ID]],INDEX(Products,0,XMATCH(Results[[#Headers],[Product Name]],Products[#Headers])),"not found")

 

can be filled to the next columns.

@Anne Walsh 

As variant

=XLOOKUP(
  Results[@[Product Name]:[Product Name]],
  Products[[Product Name]:[Product Name]],
  XLOOKUP( Results[[#Headers],[Unit Price]],
           Products[#Headers], Products),
 "not found" )

Here column names are fixed to drag entire column to the right/left and not to re-enter formula for each column.