Search code examples
excelexcel-formula

Dynamic selection: need a specific column to match 4 arguments, using xlookup and Index function

I have 3 columns of data per date (Vector, X:1, Y:1). I have 2 sheets; sheet 1 with the source data and sheet 2 where I need the data.

What I need to do: I need the X-values (X:1) (I'll worry about Y-values later) from Sheet 1 (first image) to be copied over to Sheet 2 (second image) through a dynamic selection (a drop down list based on a date (which I have)). However, the X-Values have to copied in a specific order, each 'set' of data is based off of a ring, that ring is then split into 8 locations (image 3).

I've tried using =index and vlookup and xlookup (see comments) but they don't quite work, ending up with #REF! and #VALUE! errors.

I've uploaded the current file I'm using to Google Drive. Sheet 1 is sheet 1 and Sheet 2 is X+Y (Comparison (Day Selection).

Data Range in Sheet 1

Data Selection Range

'data set'

Solution

  • Example of How to Use XLOOKUP Across Multiple Sheets:

    =XLOOKUP(A1,Sheet2!$A$1:$A$150,Sheet2!$B$1:$B$150)

    Explanation:

    A1 is the search key, that is, the value that you are using for your search.

    Sheet2!$A$1:$A$150 is where you expect the value of A1 to be found.

    Sheet2!$B$1:$B$150 is the final output as a result of the search.

    If, for example, the value in A1 is equal to the value in Sheet2 A4, the final output will be the value in Sheet2 B4.

    Use relative and absolute references as necessary when copying the formula to other cells.