CODEWITHSUNDEEP
google-sheets
Enric Delgado Claramunt
Enric Delgado Claramunt

Reputation: 11

Selecting Desired Data From Cell With Key

I have a dataset, consisting in a calendar with multiple values defined by a key. It goes like this:

enter image description here

I want to take the existing data and put it in a set of columns so I can make a dispersion graphic to see the tendency of the values. This is the format that I'm talking about:

enter image description here

What I specifically need is for the value on the cell D20 from the first dataset (which will be a timestamp from January 22nd, tracked with the key "HORA" in the cell C20) to appear in the cell B23 of the other dataset (which is the cell tracking the time at the January 22nd).

I've tried with all the lookup functions, hoping that VLOOKUP and HLOOKUP could return me an array of values that have the desired key (aka finding the key and returning cell 2), and even queries (with which I still have some issues understanding).

Thank you so much!

Upvotes: 0

Views: 42

Answers (1)

Martín
Martín

Reputation: 10185

That kind of organization of data is not very efficient for applying formulas, but obviously it is visually nice. What I can think of is trying to "offset" with XLOOKUP:

=XLOOKUP(A2,FLATTEN(MUESTRAS!C1:AA500),FLATTEN(MUESTRAS!D2:AB501),,0)

See how the ranges are moved one cell down to the right. Adapt them if needed. You can use this as an ArrayFormula too:

=INDEX(XLOOKUP(A2:A,FLATTEN(MUESTRAS!C1:AA500),FLATTEN(MUESTRAS!D2:AB501),,0))

Upvotes: 1

Related Questions