1

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

player0
  • 124,011
  • 12
  • 67
  • 124
matt3185
  • 29
  • 4

1 Answers1

0

use this formula instead:

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

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • That works on the demo sheet but on another sheet I am trying to reference all individual postcodes into their respective zones but getting NA for values in the middle of lookup ranges: http://prntscr.com/oep5oy http://prntscr.com/oep5tv Any suggestions? https://docs.google.com/spreadsheets/d/1L8rjiN5KtlIQeByijwMoRZ_iTOhv340jCeaj6NqrWPM/edit?usp=sharing – matt3185 Jul 14 '19 at 00:56