Forum Discussion

alangibson12's avatar
alangibson12
Copper Contributor
Sep 05, 2020
Solved

Lookup 2 Tables based on IF AND statement

Hi I am trying to 1. Lookup a table and reference a value based on being equal to or less than. 2. Return a value in the next column 3. Have a condition that looks up a separate table.   A nest...
excel
Formulas and Functions
  • alangibson12's avatar
    alangibson12
    Sep 05, 2020

    Riny_van_Eekelen 

    That is so good, thank you.

    Its my first post and i was hoping it would be worth asking.

     

    Thanks for tidy up as well, I would have done the same.

    I almost split the tables before upload, but thought i will split when i get the formula sorted.

    So made me laugh when you did.

    Also the drop down on 1 and 2 and naming Tables to 1 and 2

     

     

     

     

     

     

     

     

     

     

alangibson12's avatar
alangibson12
Copper Contributor
Sep 05, 2020

Riny_van_Eekelen 

 

Just playing with it a bit more and working well.

To make it work as required i have tweaked the discounts to

10.01% = 10% meaning 10% discount returns 10% Comms

see attached

 

which is fine, i am just curious if the formula or table can be structured so

10% Discount Returns 10% comms and so on eg

  • alangibson12's avatar
    alangibson12
    Copper Contributor
    Sep 06, 2020

    Riny_van_Eekelen 

     

    Thank you for additional Input

    I think i will stick with VLOOKUP for now until XLOOKUP is accross the board etc

    I will also be sharing with others so not sure whether they run 365

     

Resources