help with a formula - i think vlookup and if?

[Rachel1950]

Hi,

Im hoping someone could please help me with a formula that I am struggling with.  I have two sheets in the file.  One file is the main page that is used and the other page has the data. The main page contains 5 columns. 3 of the columns have existing data, column A is a unique number, column B is a ship to number and column C has the weight.  Column D how to ship and column e what code I would like to get this information from a formula in the data tab.  The data tab has 4 columns ship to, weight, how to ship and what code. How to ship is dependent on what the weight is. 

UNIQUE NUMBER	SHIP TO	WEIGHT	HOW TO SHIP	WHAT CODE
4084480	P5240	60
4212822	P5240	175

SHIP TO	WEIGHT	HOW TO SHIP	WHAT CODE
P5240	<=60	AIR	SPECIAL 1
P5240	>60 <=250	AIR	TO BE ADVISED
P5240	>250	SEA	D13456

what combined formula can i use to retrieve this information for me?

[#2]

@Rachel1950 

See if you can finish this off for the rest of your "Ship To" codes. I made some modifications to the data table in order to accommodate the variability of weights when using the "Range lookup" aspect of VLOOKUP. And I use FILTER to, in effect, create a subset of the data table based on the "Ship to" code itself.

Here's the basic formula, a VLOOKUP using an array produced by FILTER

=VLOOKUP(C2,FILTER(Table1[[WEIGHT]:[WHAT CODE]],Table1[SHIP TO]='FRONT PAGE'!B2),2,1)

If you're not familiar with FILTER, here's a helpful reference.

[#3 · Accepted solution]

Thank you so much I should have posted days ago :).