I need to match a text field to a source number. That’s easy I thought…
The complication appears to be that the ‘search number’ is in a single column array (many hundreds deep) and the ‘Lookup array’ is a multi column array (many thousands deep but only three wide).
In the example below I’m aiming at
=ArrayFormula(INDEX($A$2:$C$5,MATCH(1,A9=MEDIAN(A9,$A$2:$A$5,$B$2:$B$5)),3))
So.
A1=MEDIAN(A1,arraynumber1, arraynumber2)
Where arraynumber1 and arraynumber2 are integer range pairs in the lookup array. The table can be sorted in ascending order.
If the array component of the MEDIAN function is removed (I can’t find if MEDIAN can take array parameters btw) The above should return TRUE or FALSE depending if A1 falls between the two numbers.
If the expression returns TRUE I then want to pick up a value in that row, similar to VLOOKUP. I am currently using INDEX as the MATCH/INDEX pair seem to be more flexible in my particular application.
So the full (failed) formula copied through B8 to B10 looks like this.
=INDEX($A$2:$C$5,MATCH(1,A9=MEDIAN(A9,$A$2:$A$5,$B$2:$B$5)),3)
Using CSE or not I get jammed between INDEX not working or MEDIAN not working. All help much appreciated. Using MEDIAN seems efficient and readable to me, all the alternatives seem to explode into 200 characters or more as an expression.
Example table.
Source numbers
150
90
300
Lookup array
5 15 “is great”
76 102 “is getting better”
132 133 “nearly spot on”
270 1007 “is a fish”
The result I want from each number is.
150 #N/A
90 is getting better
300 is a fish
If I give the MEDIAN function the whole array it returns the median of the whole array - quite probably very useful, but not here for me.
Hopefully I’m missing a very easy point.
The #N/A message below is ‘Error Did not find value '1' in MATCH evaluation.’
