Forum Discussion
Rachel1950
Oct 02, 2024Copper Contributor
help with a formula - i think vlookup and if?
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?
- Thank you so much I should have posted days ago :).
2 Replies
Sort By
- mathetesSilver Contributor
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.
- Rachel1950Copper ContributorThank you so much I should have posted days ago :).