Why doesn't ArrayFormula work with LOOKUP function?

1
votes

I am trying to extract value (Delivery Zone Code) from column C, based on a lookup value (Zip/Postcode) in F2. The lookup value falls between min and max ranges.

enter image description here

Here is a sample sheet - https://docs.google.com/spreadsheets/d/1JOMxM9gerT5zLVW0ZJtgjiF04Sw5bAXAPqtGXmst6cQ/edit?usp=sharing

I have found a formula which works in excel:

=LOOKUP(2,1/((F2>=A2:A351)/(F2<=B2:B351)),C2:C351)

But it doesn't work in Google Sheets. I have added arrayformula based on another answer - Using LOOKUP (2,1/ function in Google Sheets

=ARRAYFORMULA(LOOKUP(2,1/((F2>=A2:A351)/(F2<=B2:B351)),C2:C351))

But this formula still returns #N/A...

In the examples, zip code 3185 should return the value MEL

0

1
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

1 Answers

0
votes

use this formula instead:

=ARRAYFORMULA(VLOOKUP(F2, 
 {INDIRECT("A2:A"&COUNTA(A:A))*1, 
  INDIRECT("D2:D"&COUNTA(A:A))}, 2))

0