Forum Discussion

Rachel1950's avatar
Rachel1950
Copper Contributor
Oct 02, 2024
Solved

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 NUMBERSHIP TOWEIGHTHOW TO SHIP

WHAT CODE

 

 

 

4084480P524060  
4212822P5240175  

 

SHIP TOWEIGHTHOW TO SHIPWHAT CODE
P5240<=60AIRSPECIAL 1
P5240>60 <=250AIRTO BE ADVISED
P5240>250SEAD13456

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

 

Formulas and Functions

2 Replies

Sort By
  • mathetes's avatar
    mathetes
    Silver Contributor
    Oct 02, 2024

    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.

    • Rachel1950's avatar
      Rachel1950
      Copper Contributor
      Oct 02, 2024
      Thank you so much I should have posted days ago :).

Resources