2

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.’

G Sheets MEDIAN Example

1

I would enter

=array_constrain(filter(C$2:C$5, (A$2:A$5 <= A8) * (B$2:B$5 >= A8)), 1, 1)

in cell B8, and copy it down the results array. Explanation:

  • filter keeps only the cells from C where A8 falls between A and B.
  • array_constrain makes sure at most one result is returned (this may be unnecessary if you are sure the ranges in the lookup array do not overlap).
  • @words, Thank you, and thank you for introducing me to 'array_constrain()' I may well use that for another function I'm working on. Re 'FILTER()', the only problem I have with using filter() is that the results are live and therefore do not sort well (using menu>Data/Sort...). The above process therefore requires a manual 'copy/paste values' task - or have I misunderstood? The reason why a sort is important is that the originating array has a lot of different data interspersed amongst it, which does need different sort parameters from time-to-time (and Filter Views btw). Any other options? – DeeKay789 Jul 25 '16 at 11:28
0

Provided your lookup array is sorted (as it is) then something like:

=if(index(B:B,match(D7,A:A,1))>=D7,index(C:C,MATCH(D7,A:A,1)),"#N/A")

copied down to suit.

WA95832 example

I've kept the results out of the first three columns for the sake of whole column references.

IF
INDEX
MATCH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.