Searching an array from bottom to top
Leon Woodward
I have a table array $E$14:$G$851. Column E just contains unique identifiers, lets say a/b/c/d etc, column F contains hundreds of numbers all less than or equal to 20 and column G contains the values I need returned.
I need a formula which will search for the unique identifier in column E, and then search UP column F and find me the FIRST value that is LESS THAN the column F result and use that result to return the adjacent value in column G.
So if I had searched for "g", the formula would then search UP column F for the first number LESS THAN 16. This would be "d" with 12. the formula would then return 25010 from column G.
I've been searching for an answer to this for weeks and haven't found any examples of excel searching an array from bottom to top. I'd strongly prefer to not change the data layout (such as flipping it).
Máté Juhász
You can get it with the following array formula (press CTRL+SHIFT+ENTER after entering it in the cell:
=INDEX(C:C,MAX(IF(OFFSET($B$1,0,0,MATCH(E1,A:A,0)-1)<VLOOKUP(E1,A:B,2,FALSE),ROW(OFFSET(B1,0,0,MATCH(E1,A:A,0)-1)),"")))
How it works:
OFFSET($B$1,0,0,MATCH(E1,A:A,0)-1)- gets a reference of B column over the ID being searchedIF(OFFSET(...)<VLOOKUP(E1,A:B,2,FALSE),ROW(OFFSET(...),"")- returns an array containing row number for rows having value in B less then row searched and blank for the othersMAX(IF(...))- select maximum value (last row with value in B fullfilling criteria=INDEX(C:C,MAX(...))- select cell in column C
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: SSHFS in fstab requests password even though user connects fine using keyfile
- Next: How to override TTL in a caching-only bind9?
Related
Related Related
- 1
Searching from Bottom to Top in Python
- 2
Searching from Bottom to Top in Python
- 3
slideDown from bottom to top (and not from top to bottom)
- 4
Java array stack, printing contents of stack from top to bottom
- 5
print 2d array diagonally from bottom to top
- 6
Html, Place items from an array in the top/bottom <div> based on a property
- 7
Scroll ListView from bottom to top
- 8
Align div from bottom to top
- 9
Load images from bottom to top?
- 10
Visitor pattern (from bottom to top)
- 11
Linear Gradient from bottom to top
- 12
Rotating Div from bottom to top
- 13
Position divs from the bottom to the top
- 14
foreach fills from bottom to top
- 15
A white place from top to the bottom
- 16
Scroll Reveal from Top to Bottom
- 17
jquery sliding from top to bottom
- 18
Visitor pattern (from bottom to top)
- 19
Fading an Image from Top to Bottom
- 20
Load images from bottom to top?
- 21
Read From Bottom to Top Batch
- 22
A white place from top to the bottom
- 23
foreach fills from bottom to top
- 24
Linear Gradient from bottom to top
- 25
How to fade in from bottom to top
- 26
Android Layout Animations from bottom to top and top to bottom on ImageView click
- 27
Android Layout Animations from bottom to top and top to bottom on ImageView click
- 28
JS+CSS How to display images from an array from top right to bottom left
- 29
Searching a string from an array of strings
Comments