Forum Discussion
Using Xlookup with match function to link on matching headings?
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
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.
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.
