Find and copy shifted values from a table over index


i tried to get the result in the yellow area with the common functions but i am not getting forward.

There is a set of numbers (green) where i want to get the VALUE_A to VALUE_C from the table below. Those Values are not at the same column position all the time.

Probably this needs several functions working together.
If there is no easy solution i will use Autohotkey, but with a big set of numbers it will take some time to collect everything.


I’m sure I’m the only one who doesn’t understand what you want! :thinking:

i enter 2,5,8,9 at A and then it should copy/show VALUE_A-C to D E and F.

one part of the solution: =HLOOKUP(“VALUE_A*”, D8:H8, 1, 0)

maybe nest that with VLOOKUP or MATCH to find the index / row number at $A?

Probably the intention was as assumed by one of the suggested solutions in the attached example.
Generally the selection of a row or a column from a table in a CellRange of a sheet is best done with the help of the INDEX() function. To get complete rows or complete columns from inside the source table you need to omit the respective index, but to indicate the omission by a blank argument position.
disask84462_guessingWhatWasMeant.ods (16.7 KB)

Also: If a formula shall return a range (array) of simple reults to a range of cells it must be entered explicitly for array-evaluation. Ctrl+Shift+Enter is the appropriate key combination.

(To an administrator: The above isn’t “similar” to what I recently posted, but exactly the same. However the previous post was deleted. Silly discourse software shouldn’t pretend to be intelligent.)

thanks for the examples, but got no clue how to include the search at the whole row if the position is not fixed, inside the MATCH function.

so i entered the HLOOKUP wildcard to the summarize sheet table one step before where i decide with filters what values i need to get (A1-A4). that workaround makes the position search redundant.

Me too…