SOLVED

Lookup 2 Tables based on IF AND statement

%3CLINGO-SUB%20id%3D%22lingo-sub-1637829%22%20slang%3D%22en-US%22%3ELookup%202%20Tables%20based%20on%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637829%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20am%20trying%20to%3C%2FP%3E%3CP%3E1.%20Lookup%20a%20table%20and%20reference%20a%20value%20based%20on%20being%20equal%20to%20or%20less%20than.%3C%2FP%3E%3CP%3E2.%20Return%20a%20value%20in%20the%20next%20column%3C%2FP%3E%3CP%3E3.%20Have%20a%20condition%20that%20looks%20up%20a%20separate%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20nested%20IF%20AND%20statement%20does%20the%20job%20but%20is%20a%20long%20formula%3C%2FP%3E%3CP%3EA%20VLOOKUP%20may%20help%20but%20i%20am%20unsure%20about%20how%20to%20incorporate%20IF%20AND%20Statement%3C%2FP%3E%3CP%3EI%20think%20I%20could%20work%20it%20out%2C%20however%20i%20am%20curious%20if%20there%20is%20another%20function%20that%20may%20achieve%20same%20thing%20quicker.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFind%20attached%20sample%20file%20which%20will%20hopefully%20explain%20where%20i%20have%20got%20to%20and%20what%20i%20am%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EAlan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1637829%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637860%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%202%20Tables%20based%20on%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F782926%22%20target%3D%22_blank%22%3E%40alangibson12%3C%2FA%3E%26nbsp%3BCouldn't%20resist%20cleaning%20up%20your%20schedule%20a%20bit%20and%20change%20the%20structure%20of%20the%20tables.%20Hope%20you%20find%20the%20attached%20workbook%20useful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637881%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%202%20Tables%20based%20on%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637881%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20so%20good%2C%20thank%20you.%3C%2FP%3E%3CP%3EIts%20my%20first%20post%20and%20i%20was%20hoping%20it%20would%20be%20worth%20asking.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20tidy%20up%20as%20well%2C%20I%20would%20have%20done%20the%20same.%3C%2FP%3E%3CP%3EI%20almost%20split%20the%20tables%20before%20upload%2C%20but%20thought%20i%20will%20split%20when%20i%20get%20the%20formula%20sorted.%3C%2FP%3E%3CP%3ESo%20made%20me%20laugh%20when%20you%20did.%3C%2FP%3E%3CP%3EAlso%20the%20drop%20down%20on%201%20and%202%20and%20naming%20Tables%20to%201%20and%202%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637923%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%202%20Tables%20based%20on%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637923%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20playing%20with%20it%20a%20bit%20more%20and%20working%20well.%3C%2FP%3E%3CP%3ETo%20make%20it%20work%20as%20required%20i%20have%20tweaked%20the%20discounts%20to%3C%2FP%3E%3CP%3E10.01%25%20%3D%2010%25%20meaning%2010%25%20discount%20returns%2010%25%20Comms%3C%2FP%3E%3CP%3Esee%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhich%20is%20fine%2C%20%3CSTRONG%3Ei%20am%20just%20curious%3C%2FSTRONG%3E%20if%20the%20formula%20or%20table%20can%20be%20structured%20so%3C%2FP%3E%3CP%3E10%25%20Discount%20Returns%2010%25%20comms%20and%20so%20on%20eg%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637953%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%202%20Tables%20based%20on%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637953%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F782926%22%20target%3D%22_blank%22%3E%40alangibson12%3C%2FA%3E%26nbsp%3BIf%20you%20have%20an%20MS365%20subscription%2C%20you%20may%20use%20XLOOKUP%20to%20achieve%20that.%20See%20attached.%20If%20you%20get%20errors%2C%20your%20Excel%20does%20not%20support%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637956%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%202%20Tables%20based%20on%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20for%20fun%2C%20this%20solution%20is%20dressed%20up%20like%20a%20Christmas%20Turkey!%3C%2FP%3E%3CP%3EI%20have%20given%20alternatives%20implementations%20with%20and%20without%20the%20Microsoft%20365%20LET%20function.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20selectedTable%2C%20CHOOSE(tblIndex%2C%20Table1%2C%20Table2)%2C%0A%20%20LOOKUP(discount%2C%20selectedTable)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

 

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

 

 

 

 

 

 

 

 

 

 

@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

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

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

@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

 

@Peter Bartholomew 

nice, thank you for input.