Lookup 2 Tables based on IF AND statement

Occasional Contributor


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.





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.


Best Response confirmed by alangibson12 (Occasional Contributor)


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













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.


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



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


@Peter Bartholomew 

nice, thank you for input.