Finding a value in an Excel array
I have to imagine the answer is somewhere but I am not sure where.
Here is the situation: I have a 6-column array of numbers starting from zero, and they increment sequentially, but not in every cell, nor in every row. If a value in, say, the third column is the same as in the second, then the fourth (and therefore the fifth and sixth) would not change. I want to be able to look up a number by finding the first instance of the array, going from left to right and then down the array, and then returning a number in a parallel array to the right of this one.
I tried using a combination of XLOOKUP and INDIRECT functions, such as (for example, finding 1):
=INDIRECT(ADDRESS(ROW(XLOOKUP(1,A:A,A:A,XLOOKUP(1,B:B,B:B,XLOOKUP(....))),COLUMN(XLOOKUP....)+8)), with the +8 representing another array 8 columns to the right, where the value I am looking up is to be found.
It almost works, but in cases such as 4, where the number first appears in the third column, remains constant for the rest of the row, and then does not change in the following row, nothing gets returned.
I have to imagine the answer is simple - any help would be great.
With arranging in one row the arrays and INDEX and MATCH functions
=INDEX(TOROW(OFFSET(A1:F7,0,8)),,MATCH(E11,TOROW(A1:F7),0))
- Filter or map nodelists in ES6
- What is the most efficient way of getting an empty array in C# in terms of time and space?
- How do I find the closest possible sum of an Array's elements to a particular value?
- Exporting an array in bash script
- Cloning an array in Javascript/Typescript
- Remove array from JSON when it appears
- Find repeated elements occuring more than Once
- why can init array in struct with array-name in C
- How to determine if one array contains all elements of another array
- Why does my nodelist contain 0 items when console logged from my JS file?
- Reading dynamic sized byte arrays as integers
- Deep Copy with Array
- Restructure a 2d array to segment row values into associatively keyed subarrays within the row
- Insertion Sort from Right to left with Javascript
- formatting an array using php
- Getting the most frequent value of an array
- php transform array into multidim array
- Date change function in PHP
- C++ replacement for C99 VLAs (goal: preserve performance)
- Azure Logic App - For Each Key/Value Pair (Answered!)
- Difference between array_push() and $array[] =
- For loop skipping final iteration
- How to create a numpy array from a pydub AudioSegment?
- Find the 2nd highest no from array, where array contains duplicate values
- Group 2d array data by a column and apply default values where a date key is missing
- Convert a 2d array into a flat associative array using two columns
- Print 2d array data on separate lines with all row values indented except for the first value in each row
- Simplify a multidimensional array of filtering conditions using recursion
- Return result set from PDO query as an associative array from two columns
- Remove empty elements from a 2d array while inside of nested foreach loops