Excel 2007 : Help populating cells according to combinations

**jenna@telstrashop** · 03-21-2010, 11:39 PM

Thank you for the responses and sorry for the late reply. I'm obviously a bit out of my depth but I compiled a simple document that hopefully should make things a bit clearer.

We have a retail business and basically we just want to set up a reward system for the guys in the shop. If you have a look at Sheet 2 of the workbook it has the lists for the drop downs that are used on Sheet 1 and next to it is a table that shows how the points are allocated according to the product, length of the sales contract and whether it's a new sale or an upgrade.

I would like the salesmen to be able to enter the data into columns A, B and C and have column D be automatically populated with the correct amount of points according to that combination.

Thank you in advanvce!

**Palmetto** · 03-22-2010, 07:43 AM

See attached. Had to fix a couple of named ranges and the Data validation (which you will need to correct in your real workbook)

A named range was created for the points table and the LOOKUP function nested within VLOOKUP to determine which column to return the values.

=VLOOKUP(A3,Points_Table,LOOKUP(LEFT(B3,1)&C3,{"N12","N24","U12","U24"},{1,2,3,4})+1,FALSE)