Sep 04 2020 11:25 PM
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 nested IF AND statement does the job but is a long formula
A VLOOKUP may help but i am unsure about how to incorporate IF AND Statement
I think I could work it out, however i am curious if there is another function that may achieve same thing quicker.
Find attached sample file which will hopefully explain where i have got to and what i am trying to achieve.
Thanks
Alan
Sep 05 2020 12:09 AM
@alangibson12 Couldn't resist cleaning up your schedule a bit and change the structure of the tables. Hope you find the attached workbook useful.
Sep 05 2020 01:12 AM
SolutionThat 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
Sep 05 2020 01:50 AM
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
Sep 05 2020 02:51 AM
@alangibson12 If you have an MS365 subscription, you may use XLOOKUP to achieve that. See attached. If you get errors, your Excel does not support it.
Sep 05 2020 02:56 AM
Just for fun, this solution is dressed up like a Christmas Turkey!
I have given alternatives implementations with and without the Microsoft 365 LET function.
= LET(
selectedTable, CHOOSE(tblIndex, Table1, Table2),
LOOKUP(discount, selectedTable) )
Sep 05 2020 02:59 AM - edited Sep 05 2020 03:00 AM
Sep 05 2020 06:52 PM
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
Sep 05 2020 06:53 PM
Sep 05 2020 01:12 AM
SolutionThat 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