Lookup against 2 values, one set, one within a range.

**bobbles22** · 11-03-2014, 09:40 AM

Hi Guys,

I have a sheet with a list of countries and postcodes we use. Certain post code ranges need to be highlighted as they have surcharges against them.

E.g.

COUNTRY	START POSTCODE	END POSTCODE	SURCHARGE
FRANCE	50000	51000	£0
FRANCE	51000	52500	£15
FRANCE	52500	53000	£20
FRANCE	53000	55000	£0

I need to be able to lookup against two values, the country and also a number that occurs within a range, so in this case, I might chose France as the country, and enter the post code of 52522 and see that it should return a value of £20.

Alternatively, if it is very hard to do this, is there a way I can take the two range limits and convert them into a list across a row of cells of all values in that range, or even better, into a list. E.g.

COUNTRY	START POST CODE	END POST CODE
FRANCE	50000	51000

Becomes

FRANCE	50000
FRANCE	50001
FRANCE	50002
FRANCE	50003
FRANCE	50004

And so on. I appreciate it may need to go through some intermediary steps to format all the data.

Many thanks in advance,

Bob

**Ace_XL** · 11-03-2014, 10:31 AM

Like this..

**Bernie Deitrick** · 11-03-2014, 10:41 AM

I would use a SUMIFS:

=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,"<="&B2,Sheet1!C:C,">="&B2)

Where your table is in A:D of Sheet1, and the country name is in A2 and the postcode of interest is in B2