Forum Discussion

rach1345's avatar
rach1345
Brass Contributor
Jul 04, 2023

excel formula help needed please

I have set up a formula to look for dangerous goods. The formula is set up in sheet 1 in column K and reads =IF(ISNUMBER(MATCH(E2,$K$2:$K$1688,0)),"GDG","N/A"). In column E is the material and column K contains all the dangerous goods. Column A contains Orders and these orders can have multiple lines with different materials e.g. one order could be 6 lines. The formula captures the dangerous goods line. On sheet2 I have a vlookup formula where it looks for the order number and then returns that field. The problem is if that order is multiple lines and the first order is not dangerous goods it doesn’t highlight that it is a dangerous goods. How can I get round this please?
Thanks
Rachel

Formulas and Functions
  • leoperdia's avatar
    leoperdia
    Brass Contributor
    Jul 04, 2023

    Hi, I'm not sure if there is any alternative keeping the same structure....I'm going to propuse to you an option to add  columns in Sheet 1.

     

    1) Add a column B.  On that contactenate Order numbre & DG Formula(A1&M1)

     

     

    2) In the vlookup of sheet 2 introduce, as lookup value, contatenation of these two colunms =VLOOKUP(B2&"GDG";sheet1!B$1:M$179;11FALSE). Column 11 return dangerous product

     

     

    I hope it works for you.

     

    Regards

    My Upwork Profile 

     

     

Resources