Forum Discussion
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 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
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
8 Replies
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.
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
