Shipping look up by country and also between a range of weight to find a value

[VickyPalmer]

Hello, I'm hoping someone could help me with the following problem please. I have a excel sheet that has the following columns: Country, Weight from, Weight to, Cost. eg United States, 1 (Kg), 1.5 (Kg), $5.
The spreadsheet has multiple countries, different weight from and to and then the cost. I need to be able to look up a specific country, then find the right weight category and retrieve the value associated with that.

Eg look up United States for 1.11Kg it will need to first identify united states then identify that 1.11Kg is between the values 1Kg--1.5Kg and retrieve the value next to that.

I've tried multiple INDEX match formula's vlookup true etc but cannot identify a formula that would work, any help will be much appreciated.

Thank you

 

[etaf]

does this work for you
NOTE the sort order of the table , is largest to smallest weight & index/match uses the -1 for match type Greater than

so in my example 1.7 will got to the value for 2
and not the lower value of 1.5
I have also added in a MAX weight - this is just a number you would never reach - with the same cost , so in my example anything over 2 up to 10000 will be the same price as 2
maybe a better way to do that

Book10
	A	B	C	D	E	F	G	H
1	Country	Weight	Return Price			country	weight	price
2	USA	4	30.5			UK	10000	3000.5
3						UK	2	3000.5
4						UK	1.5	2000.5
5						UK	1	1000.5
6						usa	10000	30.5
7						usa	2	30.5
8						usa	1.5	20.5
9						usa	1	10.5
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX(H2:H9,MATCH(B2,IF(F2:F9=A2,G2:G9),-1))

 

[VickyPalmer]

Hi, thank you for you reply so promptly, unfortunately the formula is returning a #N/A. If use your H for the price, B for the weight I want to look up, F for the country and A for the country I want to match to and G for the weight range to be looked up but can't get it to work? not sure what I may be doing wrong?

 

[etaf]

can you post a sample using XL2BB or put the spreadsheet on a share like onedrive or dropbox preferably, rather than a random sharing site

breaking down the formula

=INDEX(H2:H9,MATCH(B2,IF(F2:F9=A2,G2:G9),-1))

=INDEX(H2:H9 - this column has the PRICE to find and return
MATCH(B2 - This is the Weight you want to lookup
IF(F2:F9 - This is the column with the countries listed - NOTE you need to have all rows filled , even if same country in the table
(F2:F9=A2 - A2 is the country you want to find
G2:G9 - is the Table of weights, SORTED largest at top for each country
-1 - find the nearest value which is greater than the lookup value

you are using 2019 - so should be fine

If use your H for the price, B for the weight I want to look up, F for the country and A for the country I want to match to and G for the weight range to be looked up but can't get it to work? not sure what I may be doing wrong?

That seems correct -

can you post a sample using XL2BB or put the spreadsheet on a share like onedrive or dropbox preferably, rather than a random sharing site

you should be able to copy my example into a spreadsheet and test
Between the A and 1 - you see the copy icon

if you are copying down a sheet , you would need to fix the ranges using a $

 

[VickyPalmer]

Hi again I just realised I did a rookie mistake and didnt finish the formula with control, shift return. It now works perfectly - thank you so much for your help, I really appreciate it.

 

[etaf]

Ok, sorry , i thought 2019 did arrays automatically like 365 , but must be wrong