Using IF, AND, OR, NOT Function in Conjunction

Since it looks like the main thrust of the formula is to choose between 3 different lookups, I would probably use lookup functions [INDEX(MATCH())] for the lookup parts, then IF() functions to specify which lookup. Something like =IF(F4="London Drugs",london drugs lookup,IF(I4="Quebec",Quebec lookup,Canada lookup))

That's how I would approach the outer IF() part of the function. Do you know how to set up the lookups, or will you need help with that, too?

I am not sure what you are asking about when you ask about compiling into a single column. Perhaps I assumed that the goal was to compile the three bonus columns into one, but it certainly is not necessary. If you need separate columns, then keep them separate.

the VLOOKUP() function could work just fine here, if you rearrange your lookup tables so that "sales/hr" is the left column in the lookup tables rather than the right column. If you are more familiar and more comfortable with VLOOKUP(), I would suggest you make that change and use the VLOOKUP() function instead of INDEX(MATCH()).

How would you ID which table to use?

Im thinking that using names ranges of your tables would work?

1. change the order of your tables, put Sales/Hour 1st
2. add a row to show 0 sales and 0 bonus
3. give each table a name (I used Quebec, London and Other)
4. make sure you have real numbers in the tables, your Quebec table contains text Bonus amounts

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Monday	3/1/2017	12	17	5	Shoppers	1	Shoppers-1	Ontario	Orangeville	$ 1,424.58				$ 284.92		$ 50
3	Monday	3/2/2017	12	17	5	Rexall	2	Rexall-2	Ontario	Georgetown	$ 720.80				$ 144.16		$ -
4	Tuesday	3/3/2017	11.5	16.5	5	London Drugs	3	London Drugs-3	Ontario	Woodbridge	$ 624.48				$ 124.90	London	$ -
5	Tuesday	3/4/2017	11.5	16.5	5	Shoppers	4	Shoppers-4	Alberta	Calgary	$ 719.00				$ 143.80		$ -
6	Monday	3/5/2017	11	15	4	Shoppers	5	Shoppers-5	Nova Scotia	Halifax	$ 98.98				$ 24.75		$ -
7	Monday	3/6/2017	12	16	1	Rexall	6	Rexall-6	Quebec	Quebec	$ 108.69				$ 108.69	Quebec	$ 25
8	Tuesday	3/7/2017	12	16	4	London Drugs	7	London Drugs-7	Ontario	Burlington	$ 627.48				$ 156.87	London	$ 25
9	Tuesday	3/8/2017	12	16	4	Rexall	8	Rexall-8	Quebec	Quebec	$ 134.00				$ 33.50	Quebec	$ -

I put the location in P, then...
Q2=VLOOKUP(O2,INDIRECT(IF(P2="","Other",P2)),2,1)
copied down

I am not sure I understand what you are stuck.

Using a divide and conquer strategy, maybe let's focus on getting the lookups to work correctly. Row 2 should be pulling from the "rest of Canada" table, so does =INDEX($D$15:$D$17,MATCH(O2,$E$15:$E$17,1)) in M2 return the correct value?

Copied down, that should work for row 2 and row 8, but should return N/A for the others (because the sales/hr for the other rows is less than 150). If you added a row to the top of the "rest of Canada" table (above the 25 -- 150 entry) that was 0 -- 0 (and adjusted the references in the match and index functions), then it should work correctly for all rows. You should then be able to do something similar for N2 (for London drugs) and L2 (for Quebec).

Once you have each lookup working correctly, then a simple IF() function (similar to what I showed in post #2) should be able to choose between the three lookups. If you want to go further, they could all be nested together into a single nested function.

Can you get all three lookups working for all rows?

Assuming those functions are working, now put them into the IF() function I posted in #2: =IF(F4="London Drugs",london drugs lookup,IF(I4="Quebec",Quebec lookup,Canada lookup))
Replace the "london drugs lookup" with the INDEX(...,MATCH(...)) function that you have in column N (without the leading = sign).
Replace the "Quebec lookup" with the INDEX(...,MATCH(...)) in column L
and so on.

This variation =IF(F2="London Drugs", INDEX($G$15:$G$18,MATCH(P2,$H$15:$H$18,1))) leaves the value if false argument empty, so this function will perform the lookup when F2="london drugs" but will return FALSE when F2=anything else. Note that in my version, I put another IF() function in the value_if_false argument to test for Quebec. My function assumes that if it is neither a london drug lookup nor a quebec lookup that it should perform the rest of canada lookup. Is it necessary to perform the last "is I2="ontario" or "alberta" or..."? My example has assumed that if it is neither longond drugs nor quebec that the rest of canada lookup will apply. Are there other cases to consider?

This variation =IF(F2="London Drugs", INDEX($G$15:$G$18,MATCH(P2,$H$15:$H$18,1))), IF(I2="Quebec", INDEX(A15:A17,MATCH(P2,B15:B17,1))),IF(I2="Ontario","Alberta","Nova Scotia","Manitoba","British Columbia", "Saskatchewan", "Newfoundland", "New Brunswick", "Prince Edward Island", INDEX(D15:D18,MATCH(P2,E15:E18,1))) has multiple completely separate IF() functions separated by commas, which Excel does not know how to handle. Excel also does not know what I2="ontario","alberta",... means. I know that you mean if I2="ontario" or I2="alberta" or .... (in other words, you mean an OR() function, and this is not the correct syntax for an OR() function). To use the OR() function like this, you might do =IF(OR(I2="ontario",I2="alberta",I2="Nova Scotia",...),what to do if true, what to do if false).

The basic syntax of the IF() function is straightforward, and I would recommend that you spend some time thinking it through. The example I gave should do this in one nested IF().