Forum Discussion
Lookup 2 Tables based on IF AND statement
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 Couldn't resist cleaning up your schedule a bit and change the structure of the tables. Hope you find the attached workbook useful.
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) )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 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.
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
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
