Lookup 2 Tables based on IF AND statement

[alangibson12]

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

[#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.

[#3 · Accepted solution]

@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

[#4]

@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

[#5]

@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.

[#6]

@Riny_van_Eekelen 

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) )

[#7]

@Peter Bartholomew 

:)

Nice!

[#8]

@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

[#9]

@Peter Bartholomew 

nice, thank you for input.